Saturday, October 15, 2011

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.)
 
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_POSITIONFrom INFORMATION_SCHEMA.COLUMNSWhere 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.

Author: Tom Powell