Published:
06 Sep 2007
|
Abstract
This article provides some basic information about Information Schema in SQL Server 2005. |
|
by Uday Denduluri
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
21755/
35
|
|
|
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 |
|
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.
|
|
|
User Comments
Title:
Schema
Name:
Sankar
Date:
2008-08-29 5:57:45 AM
Comment:
It's very useful... thanks lot....
|
|
|
|