Diwali Deal : Flat 20% off + 2 free self-paced courses + $200 Voucher - SCHEDULE CALL
The system views in SQL contain internal information about a Database. The master database, for example, contains information about the SQL Server itself. The System Defined Views are predefined views already existing in the SQL Server database, such as Tempdb, Master, and temp. Each of the databases has its properties and functions. The template database for all User Defined views is from the Master database. One can master the subject with an SQL server certification, learn online SQL, and build a professional career if one wants to imagine a situation where an audit is going on in your organization and as a part of the IT team, you are asked to provide the auditor's access to certain company-related data. Now, there can be a lot of sensitive information also that you do not want the auditors to access. At the same time, there can be a lot of information that is required by the auditors. So you are in a real dilemma, what access to give and what not. You cannot provide full access to the database which would allow the auditors to access all your company-related information including sensitive ones. Neither can you deny access, which could lead you to legal trouble. You need something which you can provide access to some specific information to the auditors. That is where Views come into play.
Views in SQL are virtual tables based on the result of a query. If you have a hundred fields in one of your tables, and sixty of those fields have sensitive data, you can easily create a view in sql server on the remaining forty fields and provide access only to those forty fields. The SQL server creates view, and the user will feel like he is accessing a database table without having a faint idea that he is only given access to a part of the organization's data.
There are two types of views in SQL. Let us explore it in detail.
System-Defined View
Predefined views, known as System Defined Views, come as part of the SQL Server database installation, and they are present in databases like Tempdb, Master, and temp. Each database has its unique properties and functions.The Master database serves as the template for all User Defined Views, offering numerous predefined views that serve as templates for tables and other databases. In total, it contains nearly 230 such predefined views.System Defined Views in SQL are automatically linked to all User Defined databases. They furnish vital insights into database details, tables, and the properties associated with databases and tables. These views fall into three categories: Information Schema, Catalog View, and Dynamic Management View.
System views are of three types.
Where are System Defined Views Located?
System views are located inside the System Views tab under the View tab of each database.
Information Schema view |
Dynamic Management View |
Catalog Views |
Information schema views provide an internal, system table-independent view of the SQL Server metadata |
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance |
Catalog views return information that the SQL Server Database Engine uses |
Information Schema View
There are twenty different schema views in the SQL server. They are used to display the physical information of the database, such as tables, constraints, columns, and views. This view starts with INFORMATION_SCHEMA and is followed by the View Name. INFORMATION_SCHEMA.CHECK_CONSTRAINTS is used to receive information about any constraint available in the database.A constraint is used on a particular column in a table to ensure that certain data rules are followed for the column. INFORMATION_SCHEMA.COLUMNS receives information about the table columns, such as table name, column name, column position, default value, etc. To return the views present in the current database, INFORMATION_SCHEMA.VIEWS is used. Here is an example.
Information Schema View Examples
CHECK_CONSTRAINTS
This returns one row for each CHECK constraint in the current database. This information schema view returns information about the objects to which the current user has permission. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS]
The output looks like the one below.
COLUMN_DOMAIN_USAGE
This column domain usage returns one row for each column in the current database with an alias data type. This information schema view returns information about the objects to which the current user has permission. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[COLUMN_DOMAIN_USAGE]
The output looks like the following.
COLUMN_PRIVILEGES
This returns one row for each column with a privilege that is either granted to or granted by the current user in the current database. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[COLUMN_PRIVILEGES]
The output looks like the following.
COLUMNS
The result of this query yields a single row for each column possessing a privilege that is either assigned to or obtained by the present user within the current database. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[COLUMNS]
The output looks like the following.
CONSTRAINT_COLUMN_USAGE
This returns one row for each column in the current database with a defined constraint. This information schema view returns information about the objects to which the current user has permission. The query looks like the following.
Select * from [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE]
The output is as follows.
CONSTRAINT_TABLE_USAGE
This returns one row for each table in the current database that has a constraint defined on the table. This information schema view returns information about the objects to which the current user has permission. The query looks like the following:
select * from [INFORMATION_SCHEMA].[CONSTRAINT_TABLE_USAGE]
The output is as follows.
DOMAIN_CONSTRAINTS
This returns one row for each alias data type in the current database with a rule bound to it, and the current user can access that. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[DOMAIN_CONSTRAINTS]
The output looks like the one below.
DOMAINS
This returns one row for each alias data type that can be accessed by the current user in the current database. The query is as follows.
select * from [INFORMATION_SCHEMA].[DOMAINS]
The output looks like the one below.
KEY_COLUMN_USAGE
This query retrieves a single row for each column defined as a key constraint within the current database. It belongs to the Information Schema views, which provide details about objects accessible to the current user based on their permissions. The query is as follows:
select * from [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
The output looks like the below
PARAMETERS
This returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database. For functions, this view also returns one row with return value information. The query looks like below
select * from [INFORMATION_SCHEMA].[PARAMETERS]
The output is as follows.
REFERENTIAL_CONSTRAINTS
This returns one row for each FOREIGN KEY constraint in the current database. This information schema view returns information about the objects to which the current user has permission. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS]
The output is as follows.
ROUTINE_COLUMNS
This returns one row for each column returned by the table-valued functions that can be accessed by the current user in the current database.
select * from [INFORMATION_SCHEMA].[ROUTINE_COLUMNS]
The output is as follows.
ROUTINES
This returns one row for each stored procedure and function that can be accessed by the current user in the current database. The columns that describe the return value apply only to functions. For stored procedures, these columns will be NULL. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[ROUTINES]
The output is as follows.
SCHEMATA
This returns one row for each schema in the current database. To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA.view_name. To retrieve information about all databases in an instance of SQL Server, query the sys. databases (Transact-SQL) catalog view. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[SCHEMATA]
The output is as follows.
TABLE_CONSTRAINTS
This returns one row for each table constraint in the current database. This information schema view returns information about the objects to which the current user has permission. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS
The output is as follows:
TABLE_PRIVILEGES
This returns one row for each table privilege granted to or granted by the current user in the current database. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[TABLE_PRIVILEGES]
The output is as follows.
TABLES
This returns one row for each table or view in the current database for which the user has permission. The query is as follows.
select * from [INFORMATION_SCHEMA].[TABLES]
The output looks as follows.
VIEW_COLUMN_USAGE
This column returns one row for each column in the current database used in a view definition. This information schema view returns information about the objects to which the current user has permission. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[VIEW_COLUMN_USAGE]
The output looks like the one below.
VIEW_TABLE_USAGE
This Returns one row for each table in the current database used in a view. This information schema view returns information about the objects to which the current user has permission. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[VIEW_TABLE_USAGE]
The output is as follows.
VIEWS
This returns one row for each view that can be accessed by the current user in the current database. The query looks like the one below.
select * from [INFORMATION_SCHEMA].[VIEWS]
The output is as follows.
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
There are two types of dynamic management views and functions:
Dynamic management views can be referenced in Transact-SQL statements using two-part, three-part, or four-part names. On the other hand, dynamic management functions can be referenced in Transact-SQL statements using either two or three-part names. Dynamic management views and functions cannot be referenced in Transact-SQL statements using one-part names.All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema. For example, to query the dm_os_wait_stats dynamic management view, run the following query:
SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats;
The output is as follows.
Catalog views return information that the SQL Server Database Engine uses. We recommend using catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.
Example: The following query details a particular database's ldf and mdf files.
select * from [sys].[database_files]
The output is as below
The following are the advantages and disadvantages of Views in SQL:
Advantages |
Disadvantages |
Simplify complex queries |
Increased complexity |
Enhance data security |
Reduced performance |
Improve performance |
Limited functionality |
Enhance data consistency |
Security concerns |
Increase flexibility |
Maintenance issues |
Over the last few paragraphs; we discussed in detail different system-defined views and their uses with examples.Now that we have introduced the readers to the basics of system-defined views, they can further study the different aspects and utilities of System-defined views in a practical and real-life scenario. If you want to learn more about this subject and learn online SQL, you can opt for online SQL certification and skyrocket your career in this field.
SQL Training For Administrators & Developers
What is Schema in SQL With Example: All You Need to Know
Data Definition Language (DDL) Commands in SQL
How To Create Database Table-All You Need To know
What does a Database Administrator do? A Detailed Study
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Download Syllabus
Get Complete Course Syllabus
Enroll For Demo Class
It will take less than a minute
Tutorials
Interviews
You must be logged in to post a comment