Metadata Functions

---

Metadata Functions in SQL Server

Metadata functions in SQL returns information about the database and database objects.

All the functions are listed below along with their explanations:

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.

Syntax:

@@PROCID

Function	Description
COL_LENGTH	Returns the defined length, in bytes, of a columns.

Syntax:

COL_LENGTH ( '<table_name>', '<column_name>' )

Function	Description
ASSEMBLYPROPERTY	Returns information about a property of an assembly

Syntax:

ASSEMBLYPROPERTY ( '<assembly_name>', '<property_name>' )

Function	Description
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> )

Function	Description
COLUMNPROPERTY	Returns information about a column or procedure parameter.

Syntax:

COLUMNPROPERTY ( <id>, '<column_name>', '<property_name>' )

Function	Description
DATABASEPROPERTY	Returns the named database property value for the specified database and property name.

Syntax:

DATABASEPROPERTY ( '<database_name>', '<property_name>' )

Function	Description
DATABASEPROPERTYEX	Returns the current setting of the specified database option or property for the specified database.

Syntax:

DATABASEPROPERTYEX ( '<database_name>', '<property_name>' )

Function	Description
DB_ID	Returns the database identification (ID) number.

Syntax:

DB_ID ( [ '<database_name>' ] )

Function	Description
DB_NAME	Returns the database name.

Syntax:

DB_ID ( [ <database_id> ] )

Function	Description
FILE_ID	Returns the file identification (ID) number for the given logical file name in the current database

Syntax:

FILE_ID ( '<file_name>' )

Function	Description
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>' )

Function	Description
FILE_NAME	Returns the logical file name for the given file identification (ID) number.

Syntax:

FILE_NAME ( <file_id> )

Function	Description
FILEGROUP_ID	Returns the filegroup identification (ID) number for a specified filegroup name.

Syntax:

FILEGROUP_ID ( '<filegroup_name>' )

Function	Description
FILEGROUP_NAME	Returns the filegroup name for the specified filegroup identification (ID) number

Syntax:

FILEGROUP_NAME ( <filegroup_id> )

Function	Description
FILEGROUPPROPERTY	Returns the specified filegroup property value when specified with a filegroup and property name.

Syntax:

FILEGROUPPROPERTY ( '<filegroup_name>', '<property_name>' )

Function	Description
FILEPROPERTY	Returns the specified file name property value when a file name and property name are specified.

Syntax:

FILEPROPERTY ( '<file_name>', '<property_name>' )

Function	Description
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 } )

Function	Description
FULLTEXTCATALOGPROPERTY	Returns information about full-text catalog properties.

Syntax:

FULLTEXTCATALOGPROPERTY ( '<catalog_name>, '<property_name>' )

Function	Description
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>' )

Function	Description
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> )

Function	Description
INDEXKEY_PROPERTY	Returns information about the index key. Returns NULL for XML indexes.

Syntax:

INDEXKEY_PROPERTY ( <object_id>, <index_id>, <key_id>, '<property_name>' )

Function	Description
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>' )

Function	Description
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>' ] )

Function	Description
OBJECT_NAME	Returns the database object name for schema-scoped objects.

Syntax:

OBJECT_NAME ( <object_id> [, <database_id> ] )

Function	Description
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>' )

Function	Description
OBJECTPROPERTYEX	Returns information about schema-scoped objects in the current database.

Syntax:

OBJECTPROPERTYEX ( <object_id>, '<property_name>' )

Function	Description
SCHEMA_ID	Returns the schema ID associated with a schema name.

Syntax:

SCHEMA_ID ( [ '<schema_name>' ] )

Function	Description
SCHEMA_NAME	Returns the schema name associated with a schema ID.

Syntax:

SCHEMA_NAME ( [ <schema_id> ] )

Function	Description
SQL_VARIANT_PROPERTY	Returns the base data type and other information about a sql_variant value.

Syntax:

SQL_VARIANT_PROPERTY ( '<expression>', '<property_name>' )

Function	Description
TYPE_ID	Returns the ID for a specified data type name.

Syntax:

TYPE_ID ( '[<schema_name>.] <type_name>' )

Function	Description
TYPE_NAME	Returns the unqualified type name of a specified type ID.

Syntax:

TYPE_NAME ( <type_id> )

Function	Description
TYPEPROPERTY	Returns information about a data type

Syntax:

TYPEPROPERTY ( '<type_name>', '<property_name>' )