Tip

Exploring T-SQL metadata functions in SQL Server 2008

Part 1 | Part 2 | Part 3

Past articles on Microsoft

    Requires Free Membership to View

T-SQL have focused on T-SQL system functions and how to apply them. Part one of this series moves on to metadata functions, with details on how to view object names and IDs. Part two then examines how to view property settings for various objects, and the last section finishes with examples for returning file and filegroup information.

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:

Id_DefaultDb Id_SpecificDb Name_DefaultDb Name_SpecificDb
8 10 AdventureWorks2008 AdventureWorksDW2008


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:

Id_DefaultSchema Id_SpecificSchema Name_DefaultSchema Name_SpecificSchema
1 9 dbo Sales


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:

Id_DefaultDb Id_SpecificDb Name_DefaultDb Name_SpecificDb
1298103665 309576141 SalesPerson FactInternetSales

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:

TypeId TypeName
231 nvarchar


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.

This was first published in September 2010

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.