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:

1 SELECT OBJECT_ID ( N 'AdventureWorks2014.HumanResources.Employee' ) ;

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:

1 2 3 4 USE AdventureWorks2014 ; GO SELECT OBJECT_ID ( N 'HumanResources.Employee' ) ;

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:

1 2 3 IF OBJECT_ID ( N 'dbo.EmployeeInfo' , N 'U' ) IS NOT NULL DROP TABLE dbo . EmployeeInfo ; GO

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:

1 2 3 SELECT name , column_id FROM sys . columns WHERE object_id = OBJECT_ID ( N 'Person.ContactType' ) ;

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:

1 2 3 SELECT name , max_column_id_used FROM sys . tables WHERE schema_id = SCHEMA_ID ( N 'Person' ) ;

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:

1 2 3 4 SELECT name , is_nullable FROM sys . columns WHERE object_id = OBJECT_ID ( N 'Person.Person' ) AND user_type_id = TYPE_ID ( N 'name' ) ;

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:

1 2 3 SELECT name , schema_id FROM sys . schemas WHERE principal_id = DATABASE_PRINCIPAL_ID ( N 'dbo' ) ;

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:

1 2 3 SELECT database_id , compatibility_level , collation_name FROM sys . databases WHERE database_id = DB_ID ( ) ;

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):

1 2 3 4 IF DATABASE_PRINCIPAL_ID ( ) = 1 PRINT 'The trigger will fire.' ; ELSE PRINT 'The trigger will not file.' ;

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 :

1 SELECT FILE_IDEX ( '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:

1 SELECT FILEGROUP_ID ( 'MemOptFilegroup' )

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:

1 2 3 4 5 SELECT OBJECT_NAME ( object_id ) TableName FROM sys . columns WHERE user_type_id = TYPE_ID ( N 'name' ) AND is_nullable = 1 GROUP BY object_id ORDER BY TableName ;

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:

1 2 3 4 5 6 7 8 USE AdventureWorks2014 ; GO SELECT OBJECT_NAME ( object_id ) TableCurrentDB , OBJECT_NAME ( object_id , DB_ID ( N 'AdventureWorksDW2014' ) ) TableRefDB , name ColumnName FROM AdventureWorksDW2014 . sys . columns WHERE object_id = 677577452 ;

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:

1 2 3 SELECT name PrimaryKey , object_id ObjectID FROM sys . objects WHERE SCHEMA_NAME ( schema_id ) = 'dbo' AND type = 'PK' ;

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:

1 SELECT OBJECT_SCHEMA_NAME ( OBJECT_ID ( N 'AdventureWorksDW2014.dbo.DimEmployee' ) ) ;

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:

1 2 3 4 5 SELECT name ColumnName , TYPE_NAME ( system_type_id ) SystemType , TYPE_NAME ( user_type_id ) UserType FROM sys . columns WHERE object_id = OBJECT_ID ( N 'Person.Person' ) ;

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:

1 SELECT DB_NAME ( ) ;

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:

1 SELECT APP_NAME ( ) ;

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:

1 2 3 4 5 IF DB_NAME ( ) = 'AdventureWorks2014' PRINT 'This database is being accessed by ' + APP_NAME ( ) + '.' ; ELSE PRINT 'You are connected to the ' + DB_NAME ( ) + ' 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:

1 SELECT DB_NAME ( 10 ) ;

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:

1 2 3 4 5 SELECT index_id , index_column_id , INDEX_COL ( 'Person.Person' , index_id , index_column_id ) IndexCol FROM sys . index_columns WHERE object_id = OBJECT_ID ( 'Person.Person' ) AND INDEX_COL ( 'Person.Person' , index_id , index_column_id ) IS NOT NULL ;

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:

1 SELECT FILE_NAME ( 2 ) ;

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

1 SELECT FILEGROUP_NAME ( 2 ) ;

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:

1 SELECT OBJECTPROPERTY ( OBJECT_ID ( N 'Production.Product' ) , 'HasDeleteTrigger' ) ;

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:

1 SELECT OBJECTPROPERTY ( OBJECT_ID ( N 'Production.Product' ) , 'IsUserTable' ) ;

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:

1 SELECT OBJECTPROPERTY ( OBJECT_ID ( N 'Production.Product' ) , 'OwnerID' ) ;

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:

1 2 3 4 5 IF OBJECTPROPERTY ( OBJECT_ID ( N 'Production.Product' ) , 'TableHasNonclustIndex' ) = 1 SELECT name , object_id FROM sys . indexes WHERE object_id = OBJECT_ID ( N 'Production.Product' ) AND type = 1 ; ELSE PRINT 'The table does not have a nonclustered index' ;

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:

1 SELECT INDEXPROPERTY ( OBJECT_ID ( N 'Production.Product' ) , 'AK_Product_ProductNumber' , 'IsDisabled' ) ;

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:

1 SELECT DATABASEPROPERTYEX ( 'AdventureWorks2014' , 'Collation' ) ;

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

1 SELECT DATABASEPROPERTYEX ( 'AdventureWorks2014' , 'Status' ) ;

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:

1 SELECT SERVERPROPERTY ( 'ComputerNamePhysicalNetBIOS' ) ;

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

1 SELECT SERVERPROPERTY ( '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:

1 SELECT TYPEPROPERTY ( 'bigint' , 'Precision' ) ;

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:

1 SELECT COL_LENGTH ( 'dbo.Products' , 'ProductName' ) ;

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:

1 2 3 4 5 IF COL_LENGTH ( 'dbo.Products' , 'ProductName' ) IS NOT NULL INSERT dbo . Products ( ProductName ) VALUES ( 'widget1' ) ; ELSE PRINT 'The ProductName column does not exist.'

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

1 SELECT OBJECT_DEFINITION ( OBJECT_ID ( 'HumanResources.uspUpdateEmployeeLogin' ) )

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 CREATE PROCEDURE [ HumanResources ] . [ uspUpdateEmployeeLogin ] @ BusinessEntityID [ int ] , @ OrganizationNode [ hierarchyid ] , @ LoginID [ nvarchar ] ( 256 ) , @ JobTitle [ nvarchar ] ( 50 ) , @ HireDate [ datetime ] , @ CurrentFlag [ dbo ] . [ Flag ] WITH EXECUTE AS CALLER AS BEGIN SET NOCOUNT ON ; BEGIN TRY UPDATE [ HumanResources ] . [ Employee ] SET [ OrganizationNode ] = @ OrganizationNode , [ LoginID ] = @ LoginID , [ JobTitle ] = @ JobTitle , [ HireDate ] = @ HireDate , [ CurrentFlag ] = @ CurrentFlag WHERE [ BusinessEntityID ] = @ BusinessEntityID ; END TRY BEGIN CATCH EXECUTE [ dbo ] . [ uspLogError ] ; END CATCH ; END ;

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

1 SELECT OBJECT_DEFINITION ( OBJECT_ID ( 'Sales.vSalesPerson' ) )

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:

1 2 3 SELECT name , stats_id , STATS_DATE ( object_id , stats_id ) StatsDate FROM sys . stats WHERE object_id = OBJECT_ID ( 'Person.Person' ) ;

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:

1 SELECT NEXT VALUEFOR AdventureWorks2014 . dbo . AWSequence ;

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 IF OBJECT_ID ( N 'dbo.Products' , N 'U' ) IS NOT NULL DROP TABLE dbo . Products ; GO CREATE TABLE dbo . Products ( ProductID INT PRIMARY KEY IDENTITY ( 101 , 1 ) , ProductName NVARCHAR ( 50 ) NOT NULL ) ; GO IF OBJECT_ID ( N 'dbo.InsertProduct' , N 'P' ) IS NOT NULL DROP PROCEDURE dbo . InsertProduct ; GO CREATE PROCEDURE dbo . InsertProduct ( @ ProdName VARCHAR ( 50 ) = NULL ) AS IF @ ProdName IS NOT NULL BEGIN INSERT INTO dbo . Products ( ProductName ) VALUES ( @ ProdName ) ; SELECT SCOPE_IDENTITY ( ) ; END EXEC InsertProduct 'widget2' ;

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.