Information Schema and SQL Server 2005: ASP Alliance


Information Schema and SQL Server 2005

page

by Uday Denduluri
Feedback

Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 21766/ 24

Introduction

Information Schema is the part of the SQL-92 standard which exposes the metadata of the database. In SQL server a set of views are created in each of the database which exposes the metadata of the database. The information schema is kept in a separate schema - information schema - which exists in all databases, but which is not included in the search path by default. It is implemented as a set of views returning specially defined data types as required by the standard. The access rights of these views do not fall under the login type "dbo." Using Information Schema we can get the information about tables, views, constraints, etc.

Description

The table given below shows the views that are supported by SQL server 2005 as a part of information schema.

Table 1

View	Contains information about
COLUMNS	Columns accessible to the current user in the current database.
INDEXES	Indexes in the current database.
KEY_COLUMN_USAGE	Keys in the current database.
PROVIDER_TYPES	Data types supported in SQL Server Mobile.
TABLES	Tables accessible to the current user in the current database.
TABLE_CONSTRAINTS	Table constraints in the current database.
REFERENTIAL_CONSTRAINTS	Foreign constraint in the current database

The table given below explains the usage pattern of Information Schema.

Table 2

select * from INFORMATION_SCHEMA.Columns where table_name = "tablename"	Returns the schema information about the columns in the table.
select * from INFORMATION_SCHEMA.Tables where table_name = "tablename"	Returns the metadata information about the table name specified.
select * from INFORMATION_SCHEMA.Columns	Returns the column information of the entire tables in the database.
select * from INFORMATION_SCHEMA.Tables	Returns the table information of all the tables in the database.
select * from INFORMATION_SCHEMA.Table_Constraints where Table_name = "tablename"	Returns the constraint information of the table in the database.
select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS	Returns the information of the referential constraints in the database.

References

SQL Server 2005 - The official home page

SQL Server 2005 - MSDN

Conclusion

The functionality of the Information_Schema is often confused with sysobjects, for example "select * from sysobjects where type = 'U.'" But, there are many differences between Information_Schema and sysobjects. Sysobjects are not the part of the SQL-92 standard and cannot be relied upon using Information_Schema views displays tables on which the user has permissions.