SQL Server Metadata Functions

SQL Server Metadata Functions

SQL Server metadata functions return information about the database and database objects.

All metadata functions are nondeterministic.  This means these functions do not always return the same results every time they are called, even with the same set of input values.

Function	Description
@@PROCID	Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger. @@PROCID cannot be specified in CLR modules or the in-process data access provider. Syntax: @@PROCID
ASSEMBLYPROPERTY	Returns information about a property of an assembly Syntax: ASSEMBLYPROPERTY ( '<assembly_name>', '<property_name>' )
COL_LENGTH	Returns the defined length, in bytes, of a columns. Syntax: COL_LENGTH ( '<table_name>', '<column_name>' )
COL_NAME	Returns the name of a column from a specified corresponding table identification number and column identification number. Syntax: COL_NAME ( <table_id>, <column_id> )
COLUMNPROPERTY	Returns information about a column or procedure parameter. Syntax: COLUMNPROPERTY ( <id>, '<column_name>', '<property_name>' )
DATABASEPROPERTY	Returns the named database property value for the specified database and property name. Syntax: DATABASEPROPERTY ( '<database_name>', '<property_name>' )
DATABASEPROPERTYEX	Returns the current setting of the specified database option or property for the specified database. Syntax: DATABASEPROPERTYEX ( '<database_name>', '<property_name>' )
DB_ID	Returns the database identification (ID) number. Syntax: DB_ID ( [ '<database_name>' ] )
DB_NAME	Returns the database name. Syntax: DB_ID ( [ <database_id> ] )
FILE_ID	Returns the file identification (ID) number for the given logical file name in the current database. Syntax: FILE_ID ( '<file_name>' )
FILE_IDEX	Returns the file identification (ID) number for the specified logical file name of the data, log, or full-text file in the current database. Syntax: FILE_IDEX ( '<file_name>' )
FILE_NAME	Returns the logical file name for the given file identification (ID) number. Syntax: FILE_NAME ( <file_id> )
FILEGROUP_ID	Returns the filegroup identification (ID) number for a specified filegroup name. Syntax: FILEGROUP_ID ( '<filegroup_name>' )
FILEGROUP_NAME	Returns the filegroup name for the specified filegroup identification (ID) number. Syntax: FILEGROUP_NAME ( <filegroup_id> )
FILEGROUPPROPERTY	Returns the specified filegroup property value when specified with a filegroup and property name. Syntax: FILEGROUPPROPERTY ( '<filegroup_name>', '<property_name>' )
FILEPROPERTY	Returns the specified file name property value when a file name and property name are specified. Syntax: FILEPROPERTY ( '<file_name>', '<property_name>' )
fn_listextendedproperty	Returns extended property values of database objects. Syntax: fn_listextendedproperty ( { default | '<property_name>' | NULL }, { default | '<level0_object_type>' | NULL }, { default | '<level0_object_name>' | NULL }, { default | '<level1_object_type>' | NULL }, { default | '<level1_object_name>' | NULL }, { default | '<level2_object_type>' | NULL }, { default | '<level2_object_name>' | NULL } )
FULLTEXTCATALOGPROPERTY	Returns information about full-text catalog properties. Syntax: FULLTEXTCATALOGPROPERTY ( '<catalog_name>, '<property_name>' )
FULLTEXTSERVICEPROPERTY	Returns information related to the properties of the Full-Text Engine.  These properties can be set and retrieved by using sp_fulltext_service. Syntax: FULLTEXTSERVICEPROPERTY ( '<property_name>' )
INDEX_COL	Returns the indexed column name.  Returns NULL for XML indexes. Syntax: INDEX_COL ( '[<database_name>. [<schema_name>] . | <schema_name> ] <table_or_view_name>', <index_id>, <key_id> )
INDEXKEY_PROPERTY	Returns information about the index key.  Returns NULL for XML indexes. Syntax: INDEXKEY_PROPERTY ( <object_id>, <index_id>, <key_id>, '<property_name>' )
INDEXPROPERTY	Returns the named index or statistics property value of a specified table identification number, index or statistics name, and property name.  Returns NULL for XML indexes. Syntax: INDEXPROPERTY ( <object_id>, '<index_or_statistics_name>', '<property_name>' )
OBJECT_ID	Returns the database object identification number of a schema-scoped object. Syntax: OBJECT_ID ( '[<database_name>. [<schema_name>] . | <schema_name> ] <object_name>', [ '<object_type>' ] )
OBJECT_NAME	Returns the database object name for schema-scoped objects. Syntax: OBJECT_NAME ( <object_id> [, <database_id> ] )
OBJECTPROPERTY	Returns information about schema-scoped objects in the current database.  This function cannot be used on objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications. Syntax: OBJECTPROPERTY ( <object_id>, '<property_name>' )
OBJECTPROPERTYEX	Returns information about schema-scoped objects in the current database.  OBJECTPROPERTYEX cannot be used on objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications. Syntax: OBJECTPROPERTYEX ( <object_id>, '<property_name>' )
SCHEMA_ID	Returns the schema ID associated with a schema name. Syntax: SCHEMA_ID ( [ '<schema_name>' ] )
SCHEMA_NAME	Returns the schema name associated with a schema ID. Syntax: SCHEMA_NAME ( [ <schema_id> ] )
SQL_VARIANT_PROPERTY	Returns the base data type and other information about a sql_variant value. Syntax: SQL_VARIANT_PROPERTY ( '<expression>', '<property_name>' )
TYPE_ID	Returns the ID for a specified data type name. Syntax: TYPE_ID ( '[<schema_name>.] <type_name>' )
TYPE_NAME	Returns the unqualified type name of a specified type ID. Syntax: TYPE_NAME ( <type_id> )
TYPEPROPERTY	Returns information about a data type. Syntax: TYPEPROPERTY ( '<type_name>', '<property_name>' )