SQL Server includes a wide range of built-in functions that you can incorporate into your T-SQL expressions. One type of function that can be particularly useful, especially when working with system catalog views, is the metadata function. Metadata functions return information about the server instance, the databases on that instance, and the objects within the databases.

A metadata function is both scalar and nondeterministic. This means that the function returns only single data value and that value will not necessarily be the same each time you run the function, even if you pass in the same input values.

Many of the metadata functions focus on returning the names or IDs of objects such as tables, views, schemas, and data types, as well as databases, database files. and filegroups. Some metadata functions let you retrieve property values for database objects, the databases themselves, or the server instance. There are also functions for carrying out other tasks, such as finding a column’s length or returning an object’s definition in the form of a T-SQL CREATE statement.

The more familiar you are with the metadata functions, the faster you can find the information you need when you need it. You can use them just like you can any scalar function, often using only a simple SELECT clause to retrieve a specific bit of information.

In this article, I provide examples of T-SQL statements that use metadata functions in various ways. I created the examples on a local instance of SQL Server 2014, with most of the queries targeting the AdventureWorks2014 or AdventureWorksDW2014 databases. I’ve tried to cover as many of the functions as possible, but know that there are more, so be sure to refer to SQL Server’s documentation for additional information.

Retrieving object IDs

Each database is made up of objects such as tables, views, primary keys, check constraints, stored procedures, and sequence objects. SQL Server assigns each of these objects a unique ID. In some cases, you will need to know that ID to get specific information about the object. For example, some system views, adhering to good relational design principles, refer to an object only by its ID.

Unless you have memorized all the IDs for every object in your database, chances are you will need help in associating a name with an ID. One of the most useful metadata functions (and perhaps one of the most useful built-in functions) is OBJECT_ID, which returns the database identifier for the specified object.

For example, on my instance of SQL Server, the following SELECT statement returns 1237579447 as the identifier for the HumanResources.Employee table in the AdventureWorks2014 database:

When calling the the OBJECT_ID function, we must ensure that we target the correct object and the correct database. However, if the AdventureWorks2014 database is already the active database, we can drop its name from the argument we pass into the function:

You can use the OBJECT_ID function to find the ID of any schema-scoped object, that is, an object that is associated with or belongs to a specific schema. For example, a user table is always defined as part of a schema, even if it defaults to dbo, but a data definition language (DDL) trigger is not. You can find a list of SQL Server’s schema-scoped objects by referring to the TechNet topic sys.objects (Transact-SQL).

One of the handiest uses of the OBJECT_ID function is to verify an object’s existence before taking a specific action, such as dropping a table. In the following example, I use an IF statement to test whether the OBJECT_ID function returns a value for the dbo.EmployeeInfo table:

If the table exists, the function will return the identifier and the DROP TABLE statement will run. If the table does not exist, the function will return a NULL value and the DROP TABLE statement will not run.

When working with system views, you will often need to use the OBJECT_ID function because the view does not include the object’s name. For example, the sys.columns catalog view returns information about all columns defined within a database. If you want to find the columns for a specific table, you need to include a WHERE clause in your query that filters on the table’s object ID, as shown in the following example:

As is often the case, we know an object’s name, but not its ID. However, rather than having to join the sys.columns view to the sys.objects view to get the object name, we can simply use the OBJECT_ID function, giving us the following results:

name	column_id
ContactTypeID	1
Name	2
ModifiedDate	3

Of course, not all objects are schema-scoped. There are databases and logical files and data types and the schemas themselves. For this reason, SQL Server includes a number of other metadata functions for retrieving an object’s ID. For example, suppose we want to retrieve data from sys.tables for the Person schema. We can use the SCHEMA_ID function to pass in the schema name, rather than having to know its ID:

The SCHEMA_ID function works just like the OBJECT_ID function in terms of saving us from having to join the table or look up the ID. The SELECT statement returns the following results:

name	max_column_id_used
Address	9
AddressType	4
StateProvince	8
BusinessEntity	3
BusinessEntityAddress	5
BusinessEntityContact	5
ContactType	3
CountryRegion	3
EmailAddress	5
Password	5
Person	13
PersonPhone	4
PhoneNumberType	3

SQL Server also assigns IDs to system and user-defined data types, so it should come as no surprise that T-SQL provides the TYPE_ID function. The WHERE clause in the following SELECT statement includes the OBJECT_ID and TYPE_ID functions in order to limit the results:

The WHERE clause uses the OBJECT_ID function to return the ID of the Person.Person table and uses the TYPE_ID function to return the ID of the Name user-defined data type, giving us the following results:

name	is_nullable
FirstName	0
MiddleName	1
LastName	0

There is even a metadata function for retrieving the ID of a security principal in the current database, as shown in the following example:

In this case, the DATABASE_PRINCIPAL_ID function returns the ID of the dbo principal, which is associated with the schemas shown in the following results:

name	schema_id
dbo	1
HumanResources	5
Person	6
Production	7
Purchasing	8
Sales	9

For some metadata functions, you do not always have to pass in a value when calling the function, in which case, SQL Server uses the context of the current database. For example, the DB_ID function can return the ID of the current database without specifying the name of that database:

The statement uses the function to narrow down the data from the sys.databases catalog view, without having to specify the database name, giving us the following results:

database_id	compatibility_level	collation_name
9	120	SQL_Latin1_General_CP1_CI_AS

Being able to use a metadata function in this way can make our code more portable and provide a mechanism for verifying our environment. For example, we can use the DATABASE_PRINCIPAL_ID function to verify whether an operation should be carried out, based on the current user (principal):

In this case, I’m using PRINT statements to demonstrate the logic, but you get the point. We can construct our T-SQL code in such a way that the trigger will fire only if a specific user connects to the database (in this instance, dbo).

Before we move on to other types of metadata functions, there are a couple more of the ID type I wanted to point out, just to demonstrate the various identifiers you can retrieve. The following example uses the FILE_IDEX function to return the ID for the specified logical file, in this case, AdventureWorks2014_Log:

Note that SQL Server also supports the FILE_ID function, but that has been deprecated and will be removed in a future version of SQL Server.

If you instead want to retrieve a filegroup’s ID, you can use the FILEGROUP_ID function:

The examples shown in this section should give you a good sense of how you can use metadata functions to retrieve object IDs, but it’s also just as important to be able to retrieve object names, so let’s move on to the next set of examples.

Retrieving object names

At the top of the list of name-related functions is OBJECT_NAME, which retrieves the name of a schema-scoped object, based on the specified object ID.

The following example uses the OBJECT_NAME function in the SELECT clause to return the names of specific tables and views:

The SELECT statement returns the tables and views that contain nullable columns configured with the Name user-defined data type, giving us the following results:

TableName

Person

vAdditionalContactInfo

vEmployee

vEmployeeDepartment

vEmployeeDepartmentHistory

vIndividualCustomer

vSalesPerson

vStoreWithContacts

vVendorWithContacts

Functions such as OBJECT_NAME work within the context of the current database. For this reason, when using this type of function to retrieve information from a database other than the current one, you must specify that database when calling the function, even if you include the target database in the FROM clause.

Consider the following SELECT statement, which retrieves data from the sys.columns view in the AdventureWorksDW2014 database, but from within the context of the AdventureWorks2014 database:

 The first two expressions in the SELECT clause use the OBJECT_NAME function to retrieve the object name based on the value in the object_id column of the target table. However, the second instance of the function also passes in the database ID, giving us the following results:

TableCurrentDB	TableRefDB	ColumnName
BusinessEntityAddress	FactInternetSalesReason	SalesOrderNumber
BusinessEntityAddress	FactInternetSalesReason	SalesOrderLineNumber
BusinessEntityAddress	FactInternetSalesReason	SalesReasonKey

Because we do not specify the database in the first function instance, the returned information is actually coming from the AdventureWorks2014 database, as evidenced by the different results in the first two columns. So use caution when retrieving data from a database other than the current one. You could end up with inaccurate information without suspecting anything is wrong.

SQL Server also provides the SCHEMA_NAME function for returning the schema name for a specified ID. If no ID is specified, the function returns the name of the default schema associated with the connected user. The following SELECT statement uses the function in the WHERE clause, taking the ID from the schema_id column:

The statement uses the SCHEMA_NAME function to return information about the primary keys within the dbo schema, giving us the following results:

PrimaryKey	ObjectID
pk33	52195236
AdvisorTest3_primaryKey	84195350
PK_ErrorLog_ErrorLogID	309576141
PK__LookupTe__6D8B9C6BA3B225AC	532196946
PK__AdvisorT__8CC3310032114505	564197060
PK_AWBuildVersion_SystemInformationID	1070626857
PK_ZipCode	1079674894
PK_DatabaseLog_DatabaseLogID	1278627598
pk3	2103678542

SQL Server also supports the OBJECT_SCHEMA_NAME function, which returns the name of the schema to which a schema-scoped object belongs. To use the function, you must pass in the object’s ID as an argument. Fortunately, you can nest metadata functions, as shown in the following example:

The SELECT statement returns the name of the schema (Person) for the DimEmployee table in the AdventureWorksDW2014 database.

Another name-related metadata function is TYPE_NAME, which can return the name of either a system type or a user-defined type, as shown in the following example:

The SELECT statement returns the following results:

ColumnName	SystemType	UserType
BusinessEntityID	int	int
PersonType	nchar	nchar
NameStyle	bit	NameStyle
Title	nvarchar	nvarchar
FirstName	nvarchar	Name
MiddleName	nvarchar	Name
LastName	nvarchar	Name
Suffix	nvarchar	nvarchar
EmailPromotion	int	int
AdditionalContactInfo	xml	xml
Demographics	xml	xml
rowguid	uniqueidentifier	uniqueidentifier
ModifiedDate	datetime	datetime

As with the ID-related functions, SQL Server also includes name-related metadata functions that you can call without specifying an argument. For example, the following SELECT statement uses the DB_NAME function to return the name of the current database:

When I ran the statement, it returned AdventureWorks2014 because that was the current database, but the function’s nondeterministic nature means that it will return whatever database you’re currently connected to.

Another name-related function is APP_NAME, which returns the application name for the current session:

In my case, the function returned Microsoft SQL Server Management Studio - Query. Keep in mind, however, that the client provides the application name and it is not verified. You should avoid implementing this function as part of your security strategy.

We can also use functions such as DB_NAME and APP_NAME for verification purposes. For example, we can use the DB_NAME function to verify that the current database is the one we want, so we don’t inadvertently run commands against the wrong database:

Again, this is only a simply example, but it demonstrates how you can use these functions for verification purposes.

Note that you can also pass in an ID to the DB_NAME function (unlike the APP_NAME function), which you might need to do in certain applications:

For certain functions, you sometimes have to put in a little extra effort to get the values you want. As we saw with the OBJECT_SCHEMA_NAME function, we also included the OBJECT_ID function to get the data we wanted.

Another function that requires more work is INDEX_COL, which returns the column names that make up an index. The function takes three arguments: the table or view name, the index ID, and the index key column position. SQL Server provides no handy functions for supplying the values for the second and third arguments. However, we can get around this by going directly to the source, the sys.index_columns view:

When calling the INDEX_COL function, I passed in the index_id and index_column_id column names as the second and third arguments. I also used the INDEX_COL function in the WHERE clause to filter out NULL results. (The INDEX_COL function returns NULL for XML indexes, and I did not want to include them.) The SELECT statement returns the following results:

IndexID	ColumnID	ColumnName
1	1	BusinessEntityID
2	1	LastName
2	2	FirstName
2	3	MiddleName
3	1	rowguid

SQL Server also includes name-related metadata functions for files and filegroups. The FILE_NAME function returns the logical file name based on the file ID:

On my system, the statement returns AdventureWorks2014_Log. The FILEGROUP_NAME function works much the same way, this time returning MemOptFilegroup:

There are a couple other name-related functions, but you get the idea. Now let’s look at how to retrieve property values.

Retrieving object property values

Let’s start with the OBJECTPROPERTY function. As the name suggest, it returns property values for schema-scoped objects in the current database. When you call this function, you specify two arguments, the object’s ID and the name of the property, as shown in the following example:

The SELECT statement retrieves the value for the HasDeleteTrigger property of the Production.Product table. On my system, the statement returns 0, or false, which means no delete trigger is defined on the table. In the next example, I retrieve the value of the IsUserTable property:

This time the statement returns 1, or true, indicating that Product is a user-defined table. The majority of properties return either true of false in this way, although there are plenty of exceptions. For example, the OwnerID property shows the owner of the specified object:

On my system, the statement returns 1, but this does not mean true. Rather, it is the ID of the dbo security principal.

You can also use the property-related metadata functions for validation purposes, as we saw in earlier examples. For instance, the following T-SQL uses an IF statement to determine whether the TableHasNonclusterIndex property is set to true:

If the Product table does not have a nonclustered index, the PRINT statement will run. If it does have a clustered index, the SELECT statement will run and return the following results:

name	object_id
PK_Product_ProductID	1973582069

SQL Server also supports a number of other property-related metadata function. For example, the following SELECT statement uses the INDEXPROPERTY function to determine whether an index has been disabled:

Unlike the the OBJECTPROPERTY function, the the INDEXPROPERTY function takes three arguments: the object, the index name, and the property. (Note that you can specify a statistics name rather than an index name.)

SQL Server supports a number of property-related metadata functions and for each function supports a number of properties, too many to cover here. But let’s look at a few more simple examples to give you a sense of the amount of information available to you.

The following SELECT statement uses the DATABASEPROPERTYEX function with the Collation property to retrieve the default collation for the AdventureWorks2014 database:

The next example uses the Status property to check whether the database is online, offline, recovering, or one of several other conditions:

SQL Server also includes the SERVERPROPERTY function for gathering information about the current server instance. For example, the following SELECT statement retrieves the NetBIOS computer name:

Or you can use the function to retrieve the current SQL Server edition:

Another handy property-related function is TYPEPROPERTY, which returns a data type’s property values. The following example uses the function to find the value of the Precision property:

The statement returns a value of which is 19.

There are so many properties from which to choose that you’ll have to rely on SQL Server documentation to look them up when you need them, unless you have already memorized the ones you use most often. The properties are listed with each function’s help topic, so start there if you want to retrieve property values.

More fun metadata functions

The types of metadata functions we’ve looked at so far have generally fallen into three categories: IDs, names, and properties. However, SQL Server supports several other metadata functions that don’t quite fit into these categories. For example, you can use the the COL_LENGTH function to retrieve a column’s length in bytes:

Be aware when using the function that the length refers to total bytes, not necessarily the specified number of bytes in the column definition. In this case, the Product name column is defined with the NVARCHAR(50) type, but the SELECT statement actually returns a value of 100 because of the type’s two-byte, Unicode nature.

As with the other metadata functions we’ve looked at, you can use the the COL_LENGTH function to test for a condition before proceeding with an operation. For example, you might use the function to check for a column’s existence, similar to how you can use the OBJECT_ID function:

 SQL Server also provides the OBJECT_DEFINITION function for retrieving the T-SQL code that defines an object:

In this case, the SELECT statement will return the CREATE statement used to generate the uspUpdateEmployeeLogin stored procedure:

We can just as easily retrieve the definitions for other types of objects, such as views:

The OBJECT_DEFINITION function provides a quick and easy way to retrieve the T-SQL code when we want it. We only need to provide the object ID, made available once again through the use of the OBJECT_ID function.

Another interesting metadata function is STATS_DATE, which returns the data of the most recent statistics update on a table or indexed view:

When calling the function, we need to provide the object ID of the table or view and the statistics ID, which are readily available through the sys.stats catalog view. The SELECT statement returns the following results:

name	stats_id	StatsDate
PK_Person_BusinessEntityID	1	2014-07-17 16:11:30.067
IX_Person_LastName_FirstName_MiddleName	2	2014-07-17 16:11:31.783
AK_Person_rowguid	3	2014-07-17 16:11:31.807
_WA_Sys_00000004_693CA210	4	2015-06-05 11:37:38.303
_WA_Sys_00000002_693CA210	5	2015-08-04 17:31:51.280

If you’re using sequence objects in your database, you can use the NEXT VALUE FOR metadata function to generate the next number for the specified sequence:

You need only provide the sequence name when calling the function, qualified as necessary. For example, if you define a sequence that starts at 101 and increments by 1, the first time you call the function, you’ll receive the value 101, the second time, the value 102, and so on.

Now let’s look at one more metadata function: SCOPE_IDENTITY. The function returns that last value inserted into an IDENTITY column within the same scope as the statement that calls the function. In this case, scope refers to a module such as a stored procedure, function, or batch. For example, the following T-SQL creates the Products table, then creates the InsertProducts stored procedure, which includes the SCOPE_IDENTITY function, and finally runs an EXEC statement against the stored procedure:

After the stored procedure’s INSERT statement runs, a SELECT statement calls the SCOPE_IDENTITY function. When we run the EXEC statement for the first time, the SELECT statement returns a value of 101 (our first IDENTITY value) and continues from there, incrementing by one each time we add a row. Returning the IDENTITY value in this way can be useful when you want to log events, insert data into other tables, run triggers, or carry out other operations that might rely on that value.

SQL Server metadata functions

In this article, we’ve looked at a number of SQL Server metadata functions and the variety of information they can return, whether object IDs or names, their properties, or other types of data. SQL Server also includes a few other metadata functions, but the examples we’ve looked at here cover the majority of them and should give you a good sense of how they work.

As you dig into the metadata functions, you’ll find that leaning about them is well worth the effort. You might be surprised how useful they can be and how you’ll come to rely on that. The better you understand what metadata functions are available and how they work, the better you can take advantage of them in your T-SQL code and SQL Server applications.