System Views in SQL

Hello Friends! We all know very well that views are very important and useful features of SQL. We can define views as:

Views are virtual tables that hold the data from one or more tables. It is stored in the database. View do not contain any data itsself, it is a set of queries that are applied to one or more tables stored within the database as an object. Views are used for security purposes in databases. Views restrict the user from viewing certain columns and rows, in other words by using a view we can apply restrictions on the accessing of specific rows and columns for a specific user. A view can be created using tables of the same database or different databases. It is used to implement the security mechanism in SQL Server.

Microsoft SQL Server contains the following two types of views:



User Define View

User-defined views are created by a user depending on his requirements. We are all aware of this so I will not explain user-defined views in this article. So I will skip user defined views. Now we explain System_Views and learn what the important is of System_Views.

System-Defined View

SQL Server also contains various predefined databases, like Tempdb, Master and temp. Each database has there own properties and responsibility. The Master database is a template database for all other user-defined databases. The Master database contains many Predefine_Views that are templates for other databases and tables. The Master database contains nearly 230 predefined views.

Each user-defined database contains both types of views (System Views and User-Defined Views). All System-Defined views are for specific purposes and perform specific actions.

A System-Defined view provides information about the database, tables and all the properties of the database and tables.

Now we learn about System-Defined views.

Microsoft SQL Server contains mainly the following two types of System Views:

1. Information Schema
2. Catalog View

Today we learn about Information_Schema Views.

Information_Schema

There are nearly 21 Information schemas in System. These are used for displaying mostly physical the information of a database, such as tables, columns, constraints and views. The information schemas start from INFORMATION_SCHEMA (View Name).



Now we understand some important and useful Information_Schema (System_Views).

INFORMATION_SCHEMA.CHECK_CONSTRAINTS:

Check_Contraint is used to get the information about any constraint available in a database. A constraint is put on a specific column in a table to ensure that specific data rules are followed for a column. The data includes the check expression that is part of the Transact-SQL constraint definition. The table name is part of the constraint name. We can select a specific column and also change their order. This Information_Schema is useful when we want to determine if a specific constraint is available in a database.

Example

select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
where CONSTRAINT_NAME='My_Constraint'

Or

select CONSTRAINT_CATALOG,CONSTRAINT_SCHEMA,CONSTRAINT_NAME,CHECK_CLAUSE from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
where CONSTRAINT_NAME='My_Constraint'

Output



INFORMATION_SCHEMA.COLUMNS

The Columns method gets the details about the columns of a table. This method returns the information about the table_name, the column_name, the position of the column in the table , the default value of the column, the data_type, the maximum character length and other information.

Example

select * from INFORMATION_SCHEMA.COLUMNS

Output



The preceding query contain information about all column of all table that are present in database. We can select column of a specific table and data_type.

select * from INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME='Marks' and DATA_TYPE ='int'

Output


INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

This system view returns all the columns using a constraint. This Information_Schema is used when we need to find all the columns that contain a type of constraint. This view returns information about Table_Name, Coulmn_Name, Constrain_Name and also other information.

Example

select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Output

select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Where TABLE_NAME='tab'

INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

This system view returns information about a table that contains any type of constraint. This Information_Schema is used when we want to find all the constraint used by a table. This query returns Table_Name and Constraint_Name but doesn't return Column_Name.

Example

select * from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

Output

select * from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
Where TABLE_NAME='Salary'

INFORMATION_SCHEMA.VIEWS:

This Information_Schema returns all the views present in the current database. This system view is used to find all the information about all or a specific view of the specific database. This query returns View_Name, View_Definition, Check_Option and the updateability of the View.

Example

select * from INFORMATION_SCHEMA.VIEWS

Output

select * from INFORMATION_SCHEMA.VIEWS
Where TABLE_NAME='My_View1'

INFORMATION_SCHEMA.VIEW_TABLE_USAGE

This Information_Schema returns information about the table that contains the view. This Information_Schema determines how many and which tables a view contains. In other words the VIEW_TABLE_USAGE system view returns all table names that are used in the creation of the view.

select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE

Output

select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
Where VIEW_NAME='View6'

INFORMATION_SCHEMA.VIEW_COLUMN_USAGE:

This Information_Schema is used to find all the columns present in a view. In other words, if we want to find which columns or which tables are used in a View then we can use the VIEW_COLUMNS_USAGE System_View . It returns all the tables and columns of each table present in the view.

Example

select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

Output

select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
WHERE VIEW_NAME='View6'

 
INFORMATION_SCHEMA.TABLES

This Information_Schema returns all the tables present in the current database. It also returns all the view names present in the database. System_View returns Table_Name and Table_Types. This System_View is used when we want to find all the tables and views present in a database.

Example

select * from INFORMATION_SCHEMA.TABLES

select * from INFORMATION_SCHEMA.TABLES
Where TABLE_TYPE='Base Table'

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

This Information_Schema returns all the constraints used by a table. This Information_Schema returns a set of rows that contains constraint name, table name (that holds a constraint), constraint type and other information. This Information_Schema is useful when we want to find the information about all the constraints held by a table.

Example

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Output

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME='salary'

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

This Information_Schema returns all the key (Primary, Foreign and Unique) information held by a column. The Key_Column_Usage Information_Schema returns the column name (that holds the key), table name, type of key and all other information. This Information_Schema is useful when we want to find any information about a key.

Example

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Output

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where CONSTRAINT_NAME='PK_pkt'

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS:

This Information_Schema returns all the Referential_Constraints information held by a table. Referential_Constraint returns Constraint_Name and information about delete and update rules of a constraint. This Information_Schema is useful when we want to find information about a Referential constraint.

Example

select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Output



So we can see that Information_Schema is a very useful system view that contains information about tables, constraints, views and other information.

I hope this article helps you in understanding Information_Schema.