System Views in SQL

Introduction

Hello Friends! We all know very well that views are valuable features of SQL. We can define views as.

Views in SQL

Views are virtual tables that hold the data from one or more tables. View does not contain any data; it is a set of queries applied to one or more tables stored within the database as an object. It is stored in the database. Views are used for security purposes in databases. Views restrict the user from viewing specific columns and rows.

In other words, by using a view, we can apply restrictions on accessing particular rows and columns for a specific user. A view can be created using tables of the same 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 know 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 importance is of System_Views.

System-Defined View

SQL Server also contains predefined databases like Tempdb, Master, and temp. Each database has there own properties and responsibilities. The Master database is a template database for all other user-defined databases. The Master database contains many Predefine_Views templates for different 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 particular 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 mainly to display physical database information, such as tables, columns, constraints, and views. The information schemas start from INFORMATION_SCHEMA (View Name).

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

INFORMATION_SCHEMA.CHECK_CONSTRAINTS

Check_Contraint is used to get information about any constraint available in a database. A constraint is put on a specific column in a table to ensure that particular 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 helpful to determine if a particular 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 column's default value, the data_type, the maximum character length, and other information.

Example

select * from INFORMATION_SCHEMA.COLUMNS  

Output

The preceding query contains information about all columns of the table in the database. We can select the 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 find all the columns containing a type of constraint. This view returns information about Table_Name, Coulmn_Name, Constrain_Name, and 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 constraint. This Information_Schema is used when we want to find all the constraints a table uses. 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 particular database. This query returns View_Name, View_Definition, Check_Option, and the update ability 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 used in creating 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. It returns all the tables and columns of each table current in the picture. In other words, if we want to find which columns or tables are used in a View, we can use the VIEW_COLUMNS_USAGE System_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 rows containing the constraint name, table name (that holds a constraint), constraint type, and other information. This Information_Schema is useful when we want to find information about all the constraints a table holds.

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 a column holds. 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 helps 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 helps find information about a Referential constraint.

Example

select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS  

Output

So we can see that Information_Schema is an instrumental system view containing information about tables, constraints, views, and others.

Conclusion

I hope this article helps you understand about views in SQL, different types of views, and Information_Schema.