Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Metadata Functions

Metadata functions return information about database objects. If you're familiar with the system tables and the INFORMATION_SCHEMA views, you can get most of the information available from metadata functions yourself. However, having such functions provides a nice shortcut instead of having to remember which system tables/information schema views to query each time.

The COL_LENGTH, COL_NAME, and COLUMNPROPERTY functions query syscolumns system table. These functions return column length, name, and various properties (such as whether the column allows nulls, whether the column is an identity or unique identifier, and so on). COLUMNPROPERTY can also be used to check the properties of a stored procedure parameter. The following example returns the length of the 'qty' column in the sales table and enables us to determine whether the stor_id column allows nulls:

SELECT 
    COL_LENGTH('sales', 'qty'), 
    COLUMNPROPERTY(1237579447, 'stor_id', 'AllowsNULL')

Results:

------ ----------- 
2      0

The DB_ID(), DB_NAME(), FILE_ID(), FILE_NAME(), FILEGROUP_ID(), FILEGROUP_NAME(), OBJECT_ID(), and OBJECT_NAME() functions query sysfiles, sysfilegroups, sysobjects, or sysdatabases system tables and provide information about database objects, files and file groups.

The DB_ID() and DB_NAME() functions can be executed without any parameters; if so, they will return the ID and name of the current database. The rest of the mentioned functions require either identifier or name as a parameter. For example:

SELECT 
    FILE_ID('pubs') AS pubs_file_id, 
    FILE_NAME(1)    AS file_name_for_file1, 
    OBJECT_ID('authors')    AS object_id_for_authors, 
    OBJECT_NAME(1977058079)    AS object_name_for_1977058079

Results:

pubs_file_id

file_name_for_file1

object_id_for_authors

object_name_for_1977058079

1

Pubs

1977058079

authors


FILEPROPERTY, FILEGROUPPROPERTY, OBJECTPROPERTY, DATABASEPROPERTY, and DATABASEPROPERTYX also query sysobjects, sysfiles, sysdatabases, and sysfilegroups system tables. However, they provide additional information, unlike the previous group of functions, which provided only ID or NAME.

There are numerous properties that can be returned by these functions: whether a database file is supposed to grow or shrink automatically, whether an object has a trigger or constraints, whether a filegroup is a default filegroup, and so on. We don't have the room to discuss each of these properties here, but feel free to check out online documentation for more information.

The SQL_VARIANT_PROPERTY() and TYPE_PROPERTY() functions are similar to the functions discussed previously. TYPE_PROPERTY returns properties of a specified data type, whereas SQL_VARIANT_PROPERTY returns properties of a column or a variable declared as SQL_VARIANT. For example:

DECLARE @variant SQL_VARIANT

SELECT @variant = 'wq3er'
SELECT SQL_VARIANT_PROPERTY(@variant, 'basetype')

Results:

-------------
varchar

The INDEX_COL, INDEXKEY_PROPERTY, and INDEXPROPERTY functions query syscolumns, sysindexes, and sysindexkeys system tables. These functions return information about indexes and columns that make up the index—index keys. For example, the following query returns the first column in the clustered index (index id = 1) on the stores table:

SELECT INDEX_COL( 'stores' , 1 , 1 )
Results:
----------
stor_id

The @@PROCID function returns a procedure identifier within the sysobjects table during the execution of the current procedure.

  • + Share This
  • 🔖 Save To Your Account