Information_schema.Tables: Returns information about Tables. To return all user defined table names in the database you can write select as –

SELECT TABLE_NAME from Information_schema.Tables WHERE TABLE_TYPE = 'BASE TABLE'

Information_schema.Columns: Returns information about columns in the table. To get column name, data types and sizes for Users table, you can write SQL as –

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'Users'

For data types that have fixed size like int, datetime, the CHARACTER_MAXIMUM_LENGTH will return null.

Information_schema.TABLE_CONSTRAINTS: Returns information on all constraints. Users can get information on specific tables. The Constraints are identified by the CONSTRAINT_TYPE column.

For example, to get all constraints on Users Table you can write SQL as –

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Where Table_name = 'Users'

To get Primary key Constraint on the Users table you can write SQL as –

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Where Table_name = 'Users' AND CONSTRAINT_TYPE = 'PRIMARY KEY'

Information_Schema.CONSTRAINT_COLUMN_USAGE: Returns column information and the constraint associated with it.

For example, in the above example we got PRIMARY KEY CONSTRAINT Information on Users table but we would like to have the column name. The SQL will look like –

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = 'PK_USERS'

Combining Information_schema.Table_constraints and Information_schema. CONSTRAINT_COLUMN_USAGE, SQL will look like

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON            INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME

            WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = 'Users' AND CONSTRAINT_TYPE = 'PRIMARY KEY'

Information_schema.REFERENTIAL_CONSTRAINTS: Returns information about foreign key constraints.

For example, consider you have two tables in your database. One is users table and one is UserRoles table. Users table has UserID which is referenced in UserRoles table. To get the foreign key column information your SQL will look like –

SELECT        fkey.Table_name, fkey.Column_name

FROM             INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rkey JOIN

            Information_schema.REFERENTIAL_CONSTRAINTS Ref on rkey.CONSTRAINT_NAME = Ref.Unique_Constraint_Name

            JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fkey ON Ref.CONSTRAINT_NAME = fkey.CONSTRAINT_NAME

WHERE         rkey.Table_Name = 'Users'

The above SQL will get you the table name and column names which reference the UserID table in the Users table.

Information_Schema.ROUTINES: Returns information on Stored Procedure and functions.

To get information on all Stored Procedures in your database your SQL will be –

SELECT * FROM Information_Schema.ROUTINES Where Routine_type = 'PROCEDURE'

Information_Schema.PARAMETERS: Returns information on Parameters for stored procedure.

To get information on Parameters for AddUser stored procedure your SQL will be –

SELECT PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM Information_Schema.PARAMETERS Where Specific_name = 'AddUser'

This section explained how you can use some of the information schema views to extract data about your database.