Against SQL 2000 & 2005, Here are some select statements that you should never lose

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.