Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : Generate Random Password User-Defined Function
Built-in Functions
Home > Functions > SQL Server Metadata Functions
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>' )

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 } )
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>' )

Related Articles :