System Defined Views-Exploring The View Resources That SQL Provides

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.

What is a View in SQL?

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.

Types of Views in SQL

There are two types of views in SQL. Let us explore it in detail.

• System-defined view
• User-defined view

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.

1. Information Schema Views
2. Dynamic Management Views
3. Catalog Views

Where are System Defined Views Located?

System views are located inside the System Views tab under the View tab of each database.

Different Types of System Defined Views

We will Now Discuss Them in More Detail.

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 View

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:

• Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
• Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

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 View

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

What are The Advantages and Disadvantages of Views in SQL?

The following are the advantages and disadvantages of Views in SQL:

Conclusion

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

• No cost for a Demo Class
• Industry Expert as your Trainer
• Available as per your schedule
• Customer Support Available

Enroll for Demo Class