INFORMATION SCHEMA

Overview

• Introduced in SQL 2000
• Being used to obtain the metadata rather than going back to sys tables
• Conforms to SQL-92 standard

SQL Server Term	SQL-92 Term
Database	Catalog
Owner	Schema
Object	Object
User-defined Data Type	Domain

• Available in all databases

• View Definition can be viewed in master db
• Following are 20 views exposed by SQL Server under INFORMATION_SCHEMA

View Name	Description
Databases
Schemata	Contains information on each database on the SQL Server
Tables and Views
Columns	Contains information on each column in the current database
Tables	Contains information on each relation (table or view) in the current  database
Views	Contains information on each view in the current database
View_Column_Usage	Contains information on each column used by a view in the current database
View_Table_Usage	Contains information on each table used by a view in the current database
Constraints
Check_Constraints	Contains information on each check constraint in the current database
Constraint_Column_Usage	Contains information on each column used by a constraint in the current database
Constraint_Table_Usage	Contains information on each table with a constraint in the current database
Domain_Constraints	Contains information on each user-defined database with a rule attached
Key_Column_Usage	Contains information on each column used by a foreign or primary key in the current database
Referential_Constraints	Contains information on each foreign key constraint in the database
Table_Constraints	Contains information on each table-level constraint in the database
User-Defined Data Types
Column_Domain_Usage	Contains information on each column in the database that has a user-defined data type
Domains	Contains information on each user-defined data type in the current database
Permissions
Column_Privileges	Contains information on each column in the database where a permission has been granted to or granted by the current user
Table_Privileges	Contains information on each table in the database where a permission has been granted to or granted by the current user
Stored Procedures and User-Defined Functions *
Parameters	Contains information for each parameter of a user-defined function or stored procedure in the current database
Routine_Columns	Contains information on each column returned by a user (or system) defined function which returns table values
Routines	Contains information on each stored procedure or user-defined function in the current database

Permission

• If person has access on 10 out of 20 tables then from Information_schema, he/she will be able to see information related to those 10 tables only

• To access system tables, one should have special privilege which is not required for information_schema

Limitation

• Information_schema does not provide any info about Index

Examples

1. Generating Data Dictionary

SELECT      TABLE_NAME AS TableName,

            COLUMN_NAME ColumnName,

            DATA_TYPE DataType,

            ISNULL(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),'') Length,

            IS_NULLABLE IsNullable,

            ISNULL(COLUMN_DEFAULT,'') [Default]

FROM  INFORMATION_SCHEMA.COLUMNS

ORDER BY 1, ORDINAL_POSITION

2. Fetching info about Tables

SELECT * FROM INFORMATION_SCHEMA.TABLES

3. Fetching info about parameters of all SPs/Functions

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS

4. Fetching info about constraints

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

5. Fetching info about Foreign key

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS