Recently, I posted a script which would identify all the procedures which did not have appropriate permissions set, which is great for last minute deployment checks. I realized I needed to explore INFORMATION_SCHEMA closer as there is a LOT of useful metadata about the database available. Today's tip is about some of that useful information.

INFORMATION_SCHEMA is a specia schema which contains several views of the database metadata. Previous to these views, database developers were forced to join on several sys tables. Many of my earlier scripts for example rely on this method. However, in SQL Server 2005 system views were provided which made this task significantly easier. Here's some of the more useful views to look at. Explore them with code like this and see what they contain.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

I have found the following most useful, although in truth I suspect over time I will have use for all of them.

COLUMNS --> I frequently use this to find what tables a particular column name is found in

CONSTRAINT_COLUMN_USAGE --> identify what keys (primary, foreign) are defined, and on what fields

ROUTINES --> allows me to explore procedures and functions using this view

TABLES --> Get information about the various tables in the database

VIEW_COLUMN_USAGE --> obtain information on the fields the view contains

Here's a good link where you can find more information on the INFORMATION_SCHEMA.