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.

Posted by: MR. JOYDEEP DAS