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.
|