Exploring T-SQL metadata functions in SQL Server 2008

SQL Server 2008 supports a set of functions referred to as metadata functions. These functions let you retrieve information about a database and its objects. For example, you can retrieve the ID assigned to a database, schema, table or data type. You can also view the property settings for different types of objects, such as whether an index is clustered or a column allows null values.

Here I will introduce you to several metadata functions and provide examples that demonstrate how they work. When I created the examples, I ran them all within the context of the AdventureWorks2008 sample database. Some of the examples also reference the AdventureWorksDW2008 database, but you can easily substitute other databases.

NOTE: Although I cover many of the metadata functions, I do not cover all of them. Be sure to refer to the topic “Metadata Functions (Transact-SQL)” in SQL Server 2008 Books Online for a complete list. From there you can link to the individual function topic for a description on how that function works.

Viewing object names and IDs

The first two metadata functions we’ll look at are DB_ID and DB_NAME. As you may guess, DB_ID returns the ID of either a specific database or the current database, while DB_NAME returns the name of either a specific database or the current database. The following SELECT statement demonstrates how to use both functions:

SELECT
   DB_ID() AS Id_DefaultDB,
   DB_ID('AdventureWorksDW2008') AS  Id_SpecificDb,
   DB_NAME() AS Name_DefaultDb,
   DB_NAME(10) AS Name_SpecificDb;

The DB_ID function can take up to one argument -- a string value that is the name of a database. I do not include the argument in the first instance of DB_ID, so the function returns the ID for the current database, which in this case is AdventureWorks2008. In the second instance of DB_ID I specify the name of the AdventureWorksDW2008 database as the argument. Note, however, that the name must be enclosed in single quotes.

The DB_NAME function works the same way, except that the argument is an int value rather than a string value. In the first instance of DB_NAME the function once again assumes the current database. In the second instance of DB_NAME I specify the value 10, which on my system is the ID for the AdventureWorksDW2008 database.

The SELECT statement returns the results shown in the following table:

The first instance of DB_ID returns the value of 8, which is the ID of the AdventureWorks2008 database on my system, while the second instance of the function returns a 10. As you would expect, the first instance of the DB_NAME function returns the AdventureWorks2008 database and the second instance returns AdventureWorksDW2008.

The next two metadata functions we’ll look at are SCHEMA_ID and SCHEMA_NAME, which are similar to DB_ID and DB_NAME. The SCHEMA_ID function returns the ID associated with a schema name and takes an optional string argument (schema name). The SCHEMA_NAME function returns the name associated with a schema ID and takes an optional int argument (the schema ID). The following SELECT statement shows how both functions can be used:

SELECT
   SCHEMA_ID() AS Id_DefaultSchema,
   SCHEMA_ID('Sales') AS Id_SpecificSchema,
   SCHEMA_NAME() AS Name_DefaultSchema,
   SCHEMA_NAME(9) AS Name_SpecificSchema;

If you do not specify the schema name or ID, the schema associated with the caller is used. For example, the schema associated with me is dbo, which has an ID of 1. That means the first instance of SCHEMA_ID returns the value 1 and the first instance of SCHEMA_NAME returns the value dbo, as shown in the following results:

Notice that I use Sales as the argument for the second instance of SCHEMA_ID. As the results show, the ID associated with that schema is 9, which is verified by the results returned by the second instance of SCHEMA_NAME.

Another similar set of metadata functions are OBJECT_ID and OBJECT_NAME. The first returns the ID for any schema-scoped object, such as a table or view. The second returns the name of a schema-scoped object associated with a specific ID.

The OBJECT_ID function takes one argument -- the name of the object. If you do not specify a fully-qualified name, then the object is assumed to belong to the active database. If you want to retrieve the ID for an object in a different database, you must qualify the name by including the database name. The OBJECT_NAME function takes one or two arguments. The first argument is always the ID for the object whose name you want to retrieve. The second optional argument is the ID of the database that contains the object. You should specify the database ID if you want the name of an object in a database other than the current database.

The following SELECT statement demonstrates how to use both the OBJECT_ID and OBJECT_NAME functions:

SELECT
   OBJECT_ID('Sales.SalesPerson')
    AS  Id_DefaultDb,
   OBJECT_ID('AdventureWorksDW2008.dbo.FactInternetSales')
    AS Id_SpecificDb,
   OBJECT_NAME(1298103665)
    AS Name_DefaultDb,
   OBJECT_NAME(309576141, 10)
    AS Name_SpecificDb;

In the first instance of OBJECT_ID I specify the name of a schema-scoped object (the Sales.SalesPerson table) in the active database, which is AdventureWorks2008. In the second instance of the function I specify a fully qualified name -- the dbo.FactInternetSales table in the AdventureWorksDW2008 database.

The argument I use in the first instance of OBJECT_NAME is the ID for the SalesPerson table in the AdventureWorks2008 database. In the second instance of the function I first specify the ID for the SalesPerson table and then the ID for the AdventureWorksDW2008 database. The SELECT statement returns the following results:

As you can see, the functions return the names and IDs of the specified objects, but you can just as easily plug in the IDs and names of other types of objects. For a list of schema-scoped objects supported in SQL Server 2008, see the topic “sys.objects (Transact-SQL)” in SQL Server Books Online.

Another set of functions that let you find the names of objects and their IDs are TYPE_ID and TYPE_NAME. You use these functions to find the ID and name of a specific data type. The TYPE_ID function takes the name of the data type as its argument, while the TYPE_NAME function takes the type ID as its argument. For example, the following SELECT statement returns the ID for the nvarchar data type and returns the name of the data type whose ID is 231:

SELECT
   TYPE_ID('nvarchar') AS  TypeId,
   TYPE_NAME(231) AS TypeName;

The SELECT statement returns the following results:

As you would expect, the results confirm that the ID for the nvarchar data type is 231.

Part two: Inspecting property settings with metadata functions

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at www.rhsheldon.com.