Microsoft KB Archive/294350

= BUG: User Databases Do Not Contain INFORMATION_SCHEMA Views in SQL Server 2000 =

Article ID: 294350

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q294350



BUG #: 353936 (SHILOH_BUGS)



SYMPTOMS
In SQL Server 2000 Books Online, the topic &quot;Information Schema Views&quot; incorrectly states the following:

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



CAUSE
By design in SQL Server 2000, none of the user databases contain INFORMATION_SCHEMA views; instead, the INFORMATION_SCHEMA views are found in the Master database (system catalog).



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



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.



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.

Additional query words: information_schema system views upgrade CDW sp_attach_db setup

Keywords: kbbug kbpending KB294350

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.