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

Select name
 From sys.all_views
 Where schema_id = 3
 Order By name

name

-----------------------

CHECK_CONSTRAINTS

COLUMN_DOMAIN_USAGE

COLUMN_PRIVILEGES

COLUMNS

CONSTRAINT_COLUMN_USAGE

CONSTRAINT_TABLE_USAGE

DOMAIN_CONSTRAINTS

DOMAINS

KEY_COLUMN_USAGE

PARAMETERS

REFERENTIAL_CONSTRAINTS

ROUTINE_COLUMNS

ROUTINES

SCHEMATA

TABLE_CONSTRAINTS

TABLE_PRIVILEGES

TABLES

VIEW_COLUMN_USAGE

VIEW_TABLE_USAGE

VIEWS

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.

Select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
 From INFORMATION_SCHEMA.TABLES
 Where TABLE_NAME like '%Address%'

TABLE_CATALOG            TABLE_SCHEMA           TABLE_NAME          TABLE_TYPE

------------------------ ---------------------- ------------------- ------------------

AdventureWorks           Person                 Address             BASE TABLE

AdventureWorks           Person                 AddressType         BASE TABLE

AdventureWorks           Purchasing             VendorAddress       BASE TABLE

AdventureWorks           Sales                  CustomerAddress     BASE TABLE

AdventureWorks           HumanResources         EmployeeAddress     BASE TABLE

Finding Colunms

Suppose you are considering changing column data types from DateTime to DateTime2. Rather than cracking each table in SSMS this might be a good place to start.

Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION
From INFORMATION_SCHEMA.COLUMNS
Where DATA_TYPE Like '%Date%'
Order By TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE ORDINAL_POSITION

------------------------ ------------------- ----------------------- ----------------- -----------------------------

dbo AWBuildVersion VersionDate datetime 3

dbo AWBuildVersion ModifiedDate datetime 4

dbo DatabaseLog PostTime datetime 2

dbo ErrorLog ErrorTime datetime 2

HumanResources Department ModifiedDate datetime 4

HumanResources Employee BirthDate datetime 7

HumanResources Employee HireDate datetime 10

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].%'

ROUTINE_NAME

------------------------------------

uspGetBillOfMaterials

uspGetWhereUsedProductID

usp_BobsPeeps_S

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.