BUG: User Databases Do Not Contain INFORMATION_SCHEMA Views in SQL Server 2000 (294350)

SYMPTOMS

In SQL Server 2000 Books Online, the topic "Information Schema Views" incorrectly states the following:

"These views are defined in a special schema named INFORMATION_SCHEMA, which is contained in each database."

WORKAROUND

The following list shows the system INFORMATION_SCHEMA views that do not exist in any user database:

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

Although the above INFORMATION_SCHEMA views are found in the Master database in SQL Server 2000, you can still use these views to query the user databases. For example, the following command executes and returns proper results about all of the user tables in the Northwind database:

USE Northwind
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES
GO

MORE INFORMATION

If you try to upgrade user databases from SQL Server 7.0 to SQL Server 2000 by using either the Copy Database Wizard or the sp_attach_db stored procedure, the user databases in SQL Server 2000 will not contain the INFORMATION_SCHEMA views. The same applies if you try to upgrade your existing SQL Server 7.0 default instance to SQL Server 2000 using SQL Server 2000 Setup. This behavior is by design.