I have left this undocumented for long enough.
Below are Views that are visible on Master DB on your SQL Server, however you can call them from within your current DB.
I will explain some of the more useful ones.
Select * from INFORMATION_SCHEMA.TABLES
Provides a list of Tables in your current DB. DB is Catelog.
Table Type distinguishes from table (BASE TABLE) and view (VIEW).
Select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES
Who granted what privileges to whom, and what type of privilege.
Select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
If you work with foreign keys, then this is your best spot to query what the constraints are. You can use this for testing queries prior to executing. If you want to avoid handling SQL exceptions, this is what you want to test against.
Select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Use with TABLE_CONSTRAINTS, for column names on constraints
Select * from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
Pretty much like the constraint views above.
Select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Pretty much like the constraint views above, but with columns.
Select * from INFORMATION_SCHEMA.VIEWS
You can get the actual SQL statement that the view is made up out of. This is one of those things where I say it’s neet, but I have found nothing practical for it.
Select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
From here, you can see the view’s dependancies. This is one of my favorite features.
Select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
This is pretty much the same as above, but it adds the column.
Select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
Like above but with the rules. Very useful when testing constraints before committing to DB.
Select * from INFORMATION_SCHEMA.SCHEMATA
Character set schema. I rarely use this.
Select * from INFORMATION_SCHEMA.ROUTINES
This is a great tool for auditing. It shows stored proceedures, who created them, when they were created, when last altered, and a ton of extra information. Jokingly, I call this my developer’s dream query.
Select * from INFORMATION_SCHEMA.PARAMETERS
Another great tool. Stored proceedure, parameter mode, ordinal position, parameter name (with the @ symbol), data type, length, precision, scale, and a lot of stuff you usually don’t need.
Select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS
Contains one row for each column returned by the table-valued functions accessible to the current user in the current database.
The INFORMATION_SCHEMA.ROUTINE_COLUMNS view is based on the sysobjects and syscolumns system tables.
To retrieve information from this view, specify the fully qualified name of INFORMATION_SCHEMA view_name.
Note: This usually yeilds useless information for me. This is more in the category of a very advanced DBA manager.
Select * from INFORMATION_SCHEMA.DOMAINS
Contains one row for each user-defined data type accessible to the current
user in the current database. The INFORMATION_SCHEMA.DOMAINS view is based on the spt_data type_info, systypes, syscomments, sysconfigures, and syscharsets system tables.
You can use this as a validation check prior to using or calling a user-defined data type.
Select * from INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
Contains one row for each user-defined data type, accessible to the current user in the current database, with a rule bound to it. The INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS view is based on the sysobjects and systypes system tables. To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA view_name.
Usually this returns an empty set for me. Not enough companies use constraints to protect their data quality.
Select * from INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
If the other DOMAIN queries return something, then this is useful. Returns Table Name and Column Name as the primary benefit for the query.
Select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
This is a fun one. Use this for your user rights. It returns the constraint name and check clause for your DB.
Select * from INFORMATION_SCHEMA.COLUMNS
Table columns, types, sixe, nullable, ect. This is what I use when I am going to write a code generator.
Select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES
Who has rights to do what. Privilege Type is basicly Refrences, Select, and Update.
On the practical side, this is useful for auditing schema changes and building code generators.
I also find some of these good for pre-comitting checks against constraints for adhock calls against the DB. No, I am not promoting adhock calls to the DB. Do not break good n-Tier application structure. I am just laying out the option.