Like this article? We recommend
Like this article? We recommend
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.