How to use INFORMATION_SCHEMA Views in SQL Server

In SQL Server, INFORMATION_SCHEMA Views are a set of views provided by the INFORMATION_SCHEMA database in the SQL server that stores the metadata of the databases, tables, columns, and other objects stored in the system. In this article, we will learn how to use INFORMATION_SCHEMA Views in SQL Server.

In SQL Server, INFORMATION_SCHEMA Views is a collection of views stored in the INFORMATION_SCHEMA database that allows users to retrieve metadata about various objects such as tables, constraints, procedures, and views from a database. The INFORMATION_SCHEMA database consists of several views, out of which not every view is crucial for the users.

Here is a list of views stored in the INFORMATION_SCHEMA database that are used commonly by the users in SQL Server:

• INFORMATION_SCHEMA.TABLES: Provides information about all the tables present in the current database.

• INFORMATION_SCHEMA.COLUMNS: Stores information about the columns of a specific table or a view.

• INFORMATION_SCHEMA.VIEWS: Provides information about all the views of the current database.

• INFORMATION_SCHEMA.ROUTINES: Provides information about the stored procedures and functions.

• INFORMATION_SCHEMA.CONSTRAINTS: Stores information about the constraints defined on a specific table in a database.

To use the INFORMATION_SCHEMA views mentioned above, we can follow the below syntax:

Syntax:

INFORMATION_SCHEMA.view_Name

where view_Name denotes the category of the INFORMATION_SCHEMA view you want to use.

To understand how we can use different categories of the INFORMATION_SCHEMA views in SQL Server, we will consider the following table Customers as shown below:

To query INFORMATION_SCHEMA.TABLES views you can use the following Syntax:

Syntax

SELECT  Required Columns 
FROM INFORMATION_SCHEMA.TABLES;

Example: To get information about all the tables stored in your database you can execute the following query:

Query:

SELECT TABLE_NAME, TABLE_TYPE 
FROM INFORMATION_SCHEMA.TABLES;

Output:

Explanation: The above query returns an output table having two columns table_name and table_type. The first column table_name denotes the name of the tables present in the database and the second column denotes each table type.

To query INFORMATION_SCHEMA.COLUMNS views you can use the following Syntax:

Syntax:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table_Name';

Example: To get information about all the columns of a specific table in your database you can execute the following query:

Query:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers';

Output:

Explanation: The above query returns an output table having four columns TABLE_NAME, COLUMN_NAME, DATA_TYPE, and IS_NULLABLE, where the first column denotes the name of the table, the second column denotes the name of each column, the third column DATA_TYPE denotes the data type of each column and the last column denotes whether the column can consist of null values or not.

To query INFORMATION_SCHEMA.VIEWS can use the following Syntax:

Syntax:

SELECT Required Columns
FROM INFORMATION_SCHEMA.VIEWS;

Example: To get information about all the views present in your database you can execute the following query:

Query:

SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS;

Output:

Explanation: The above query returns an output table having a single column VIEW_DEFINITION which denotes the definition that was used to create a particular view in the database.

To query INFORMATION_SCHEMA.ROUTINES views you can use the following Syntax:

Syntax:

SELECT Required Columns,
FROM INFORMATION_SCHEMA.ROUTINES;

Example: To get information about all the stored procedures in the database you can execute the following query:

Query:

SELECT SPECIFIC_NAME, ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES;

Output:

Explanation: The above returns an output table having 5 columns. Where each column of the output table provides information about the stored procedures of the database.

To query INFORMATION_SCHEMA.CONSTRAINTS views you can use the following Syntax:

Syntax

SELECT Required Columns from
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'Table_Name;

Example: To get information about the constraints defined on your tables in the database you can execute the following query:

Query:

SELECT CONSTRAINT_NAME, CONSTRAINT_SCHEMA, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Where TABLE_NAME = 'Customers';

Output:

Explanation: The above query outputs a table having 3 columns CONSTRAINT_NAME, CONSTRAINT_SCHEMA, and CONSTRAINT_TYPE, where the first column denotes the unique name assigned to the constraint, the second column denotes the schema for the constraint and the last column denotes the type of the constraint.

In this article, we have learned about the INFORMATION_SCHEMA Views in SQL Server. We have learned that INFORMATION_SCHEMA views are a set of views stored in the INFORMATION_SCHEMA database that can be queried to extract crucial metadata about the databases, tables, and views in SQL Server. However, it is important to note that, the INFORMATION_SCHEMA views may not return all the information available in the system catalog views. Sometimes, you will have to query system catalog views directly to retrieve the required data.