INFORMATION SCHEMA

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

INFORMATION_SCHEMA

Introductions

When we are taking about any RDBMS, it should be a data dictionary or metadata. MS SQL Server has no exceptions.

In MS SQL Server there are two sources to view the metadata information.  

1.    Various System Tables

2.    INFORMATION_SCHEMA views

In this article I am trying to discuss about the INFORMATION_SCHEMA.

Point to Focus

1.    Metadata

2.    Anything wrong to pull information from system table

3.    About INFORMATION_SCHEMA view

4.    Descriptions of views

5.    Using INFORMATION_SCHEMA

Metadata

First we understand that what metadata is. In common understanding about metadata, we can say that it is "data about data". In the context of database it means "Information (data) stored about data, the structures or objects related to data".

Anything wrong to pull information from system table

As per Microsoft, information wise there is no difference but Microsoft reserves the rights to change any system table from version to version. So if an application depends on the system table can face a serious problem due to version change. Is it true? I don't find such an example in my development career. But we should remember the statutory warning of Microsoft.

About INFORMATION_SCHEMA view

SQL Server makes available the Information Schema Views through INFORMATION_SCHEMA schema. It is available in each database and storing information about all database objects contained in the respective database.  The following query is used to retrieve the views available under the INFORMATION_SCHEMA schema and their definitions.

SELECT s.name [Schema_Name],

       o.name [Object_Name],

       sm.definition [Schema_Defination]

FROM   sys.all_sql_modules sm

       INNER JOIN sys.all_objects o ON sm.object_id = o.object_id

       INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

WHERE  s.name = 'INFORMATION_SCHEMA'

ORDER BY o.name;

Here most of the views names are self describing.

Descriptions of Views

Here are the lists of views and there descriptions are mentioned bellow. For better understanding please refer to MSDN.

No	View	Description
1.	CHECK_CONSTRAINTS	Returns one row for each CHECK constraint
2.	COLUMN_DOMAIN_USAGE	Returns one row for each column that has an alias data type
3.	COLUMN_PRIVILEGES	Returns one row for each column that has a privilege that is either granted to or granted by
4.	COLUMNS	Returns one row for each column
5.	CONSTRAINT_COLUMN_USAGE	Returns one row for each column that has a constraint defined on it
6.	CONSTRAINT_TABLE_USAGE	Returns one row for each table that has a constraint defined on it
7.	DOMAIN_CONSTRAINTS	Returns one row for each alias data type that has a rule bound to it
8.	DOMAINS	Returns one row for each alias data type
9.	KEY_COLUMN_USAGE	Returns one row for each column that is constrained as a key
10.	PARAMETERS	Returns one row for each parameter of a user-defined function or stored procedure
11.	REFERENTIAL_CONSTRAINTS	Returns one row for each FOREIGN KEY constraint
12.	ROUTINES	Returns one row for each stored procedure and function
13.	ROUTINE_COLUMNS	Returns one row for each column returned by table-valued functions
14.	SCHEMATA	Returns one row for each schema
15.	TABLE_CONSTRAINTS	Returns one row for each table constraint
16.	TABLE_PRIVILEGES	Returns one row for each table privilege that is granted to or granted by
17.	TABLES	Returns one row for each table
18.	VIEW_COLUMN_USAGE	Returns one row for each column that is used in a view definition
19.	VIEW_TABLE_USAGE	Returns one row for each table that is used in a view
20.	VIEWS	Returns one row for views

Using INFORMATION_SCHEMA

Here is the simple example to understand the information schema.

1.  TABLEs

SELECT * FROM information_schema.tables

WHERE TABLE_TYPE = 'BASE TABLE';

SELECT * FROM information_schema.tables

WHERE  TABLE_TYPE = 'BASE TABLE'

       AND table_name = 'xyz';

      

SELECT * FROM information_schema.tables

WHERE  TABLE_TYPE = 'BASE TABLE'

       AND table_name = 'xyz'

       AND table_schema = 'abc';

2.  VIEWs

SELECT * FROM information_schema.tables

WHERE  TABLE_TYPE = 'VIEW';

3.  List CONSTRAINTs

it retrieves all the constraints of Table objects "XYZ"

SELECT constraint_name, constraint_type

FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE  table_name = 'xyz';

4.  List FUNCTIONs

SELECT routine_name

FROM   INFORMATION_SCHEMA.ROUTINES

WHERE  routine_type = N'FUNCTION';

Hope you like it.