Using Information Schema Views

Microsoft added information schema views in SQL Server 7.0 to allow safe access to metadata from the system tables. According to SQL Server 2000 Books Online (BOL), using information schema views is one of only three supported methods of obtaining metadata (you can also use system stored procedures or system functions). In "Investigating System Objects," January 2002, InstantDoc ID 23207, I briefly looked at information schema views and noted that Microsoft created these kinds of utilities so that people wouldn't have to access the system tables directly—a method that Microsoft discourages. SQL Server 2000 manages information schema views differently than SQL Server 7.0 does, so you must pay attention to which version you're using when you read about information schema views.

Viewing the Difference
Much of the technical description of information schema views in SQL Server 2000 BOL seems to be left over from SQL Server 7.0 BOL. In "Investigating System Objects," I explained that in SQL Server 7.0, each database has its own set of information schema views. SQL Server 2000 has only one set—in the master database. But when you reference one of these views from within a user database, the contents of the view reflect your current database. However, you'll find that the SQL Server 2000 BOL documentation assumes that separate sets of information schema views still exist in each database.

For example, let's consider the TABLES view, which lists all the tables and views in a database (a view is considered a virtual table). If you use the following query to look at the contents of the TABLES view in the master database, you'll see all the information schema views—and all the user tables and user-defined views.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

The names of the information schema views are in all uppercase characters. I never create object names by using all uppercase characters, so I can easily see that this query, when executed from the master database on a SQL Server 2000 server, returns the names of the information schema views with all the other user table and view names. But if you run this query in any other database in SQL Server 2000, you won't see the information schema views. And if you run this query in SQL Server 7.0, you'll see the names of the information schema views regardless of which database you're in when you run the query.

Figure 1, page 8, shows the names of the 20 information schema views in SQL Server 2000. I retrieved this list by using the following query:

SELECT  TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
AND TABLE_SCHEMA = 'INFORMATION_SCHEMA'

As I mentioned in "Investigating System Objects," Microsoft added information schema views to SQL Server for ANSI compliance. However, the ANSI-supported methods for referencing objects in a database are different from the methods that SQL Server uses. ANSI doesn't support the concept of an object owner; instead ANSI defines a schema. SQL Server 2000 BOL defines a schema as "in the SQL-92 standard, a collection of database objects that are owned by a single user and form a single namespace. A namespace is a set of objects that cannot have duplicate names. For example, two tables can have the same name only if they are in separate schemas, no two tables in the same schema can have the same name." These differences lead to cumbersome object names and the need to use the username Information Schema when accessing the objects. This username lets you access SQL Server objects in a manner similar to the ANSI standard.

BOL also says, "In Transact-SQL, much of the functionality associated with schemas is implemented by database user Ids." So, whereas Information Schema might seem strange for a username in SQL Server, the name sounds more reasonable as the name of a schema or a collection of objects.

Using the Views
Two common database-administration questions are "How can I tell whether a column is a foreign key?" and "How can I find all the foreign keys that reference a particular table?" Let's look at how you can use information schema views to find the answers. I'd like to thank SQL Server Most Valuable Professional (MVP) Dejan Sarka for providing this solution. I've changed the object names so that the solution is generic.

Before we examine the code for accessing foreign key information, let's review the vocabulary we use to talk about foreign keys. You're probably aware that a referential constraint is the same as a foreign key constraint, but many names exist for the two tables involved in foreign key relationships. Some people call the tables the master and detail tables; others dub them the parent and child tables. Both conventions imply a hierarchy, suggesting that one of the tables is superior to the other. In a relational database, this hierarchy doesn't exist. Two tables simply have a relationship—neither is superior to the other. So I call the two tables the referenced table and the referencing table, as Joe Celko suggests in his book Joe Celko's SQL for Smarties: Advanced SQL Programming, 2nd edition (Morgan Kaufmann Publishers, 1999).