INFORMATION_SCHEMA.101

If you are new to SQL Server you may have trouble finding the tables you are looking for in your database or which columns in any table use the SmallDateTime data type. You can use SQL Server Management Studio (SSMS) to track this down but it will be neither pretty nor quick. But suppose you need to know which stored procedures have cross database joins to a specific database or which user-defined functions have a hardcoded product name in a Case statement - SSMS won't give you much help. If only there were a simple and standardized way to find this information using TSQL - wouldn't that be great? Well, as it happens, that data is available in the INFORMATION_SCHEMA views (ISVs) in each database.

From BOL: "Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA."

This means you can use the same code to get metadata from any version of SQL Server from SQL 2000 on. There are other ways to get this information by querying system objects but these may change from release to release. In fact since ISVs conform to the ISO standard it also works on other platforms such as DB2.

Note that the information given is primarily for user defined database objects rather than system metadata. Here is a list of ISVs along with the code that produced the list. (I find it ironic there isn't an ISV for this.)

We are not going to look at all the views, instead we will focus on three of the most useful for the average DBA to use cruising around a database. Once you get the feel for the naming conventions in these views you will find the rest familiar also.

Finding Tables

If you need to know where all the address data in your DB is stored you might write the following query.

The ORDINAL_POSITION is also handy when you are trying to locate a single column out of a 150 column table. (Oh, come on, you know you have them.)

Finding Code

Finally, let's see how you can query against the T-SQL code in stored procedures and user-defined functions. Suppose you have an instance with multiple DBs that have been together for a long time and have grown intertwined through cross-database joins. Not a problem as long as everybody stays on the instance but suppose you need to move one of them? You can query the ROUTINES view to find cross-database joins (or any string) in the underlying code as follows:

Select ROUTINE_NAME
From INFORMATION_SCHEMA.ROUTINES
Where ROUTINE_DEFINITION Like '%Bob.%'
Or ROUTINE_DEFINITION Like '%[Bob].%'

This will return the name of every user-defined function or stored procedure in the DB with a cross-database join to Bob.

There is one peculiarity you should be aware of here though. Since the ROUTINE_DEFINITION column is nvarchar(4000) an exceptionally large chunk of code will generate multiple rows in the view. If, and it's a big if, the string you are looking for falls across the 4000 character mark the search will not return a row. You could code for this but I, being a good Calvinist, can accept that I was not ordained to find the string and move on with my life.

Summary

The INFORMATION_SCHEMA views are an easy to use set of tools to get user object metadata from any (modern) version of SQL Server as well as other major relational database engines. The naming convention is consistent and the ISO standardization ensures future compatibility.