Intro to Information Schema Views

These views are to extract the metadata about your database. They are very useful for documentation and analysis.

Here is the list of views in SELECT format to get you started.

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.DOMAINS
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.VIEWS

From here you can start your analysis. If you are looking for all tables with a key column (or any column) 

SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMN_NAME = 'MyKeyID'

What tables does a view access? Just use:

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE


The INFORMATION_SCHEMA.COLUMNS view will get a lot of use. This is where I usually start. You can scan column and table names and get a list of related items. Then you can branch out to other views as needed.