INFORMATION_SCHEMA VIEWS V2K

Teaching old dogs new tricks is difficult. Longtime SQL Server Database
Administrators (DBAs) using the system catalog or system tables to
retrieve information about their server, tables, and database objects 
have been
doing so for some time; this, despite the fact that Microsoft does not
recommend the practice of using system tables to obtain information. 
Some
of these tables have changed over the years and can present problems 
when
it comes time to migrate an application to the next version.

Although INFORMATION_SCHEMA views have been around for a while, they
have been ignored for the most part by the seasoned DBA. They provide a
wealth of information and are less likely to change drastically from 
version
to version. The following is a list of the INFORMATION_SCHEMA views
provided in SQL Server. Most of the information is specific to the 
owner
executing the select statement.

Use a simple select statement to obtain the data and substitute
INFORMATION_SCHEMA as the user.

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS

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

HAPPY HOLIDAYS FROM TECHREPUBLIC

Due to the holidays, look for your next SQL Server e-newsletter on Jan.
7, 2002. TechRepublic wishes all of our members a wonderful holiday.

----------------------------------------