LogoASPAlliance: Articles, reviews, and samples for .NET Developers
Information Schema and SQL Server 2005
by Uday Denduluri
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 22852/ 53


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.


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

Table 1


Contains information about


Columns accessible to the current user in the current database.


Indexes in the current database.


Keys in the current database.


Data types supported in SQL Server Mobile.


Tables accessible to the current user in the current database.


Table constraints in the current database.


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.


Returns the information of the referential constraints in the database.


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.

©Copyright 1998-2021  |  Page Processed at 2021-12-01 8:30:10 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search