Tuesday, May 10, 2016

SQL - System Views

System views are  helpful to do query for various information about the system state by using SQL commands. They always located in the SYS schema. In a system with multi-tenant databases with SQL Databases containers, every database has own SYS schema with system views that contain information about that database only. In addition, the system database has a further schema, SYS_DATABASES, which contains views for monitoring the system as a whole
System Views

System views expose catalog metadata. You can use system views to return information about the instance of SQL Server or the objects defined in the instance. For example, you can query the sys.databases catalog view to return information about the user-defined databases available in the instance. Microsoft SQL Server provides the following collections of system views that expose metadata:
  1. Catalog ViewsCatalog views return information that is used by the SQL Server Database Engine and 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.
  2. Information Schema Views: An information schema view is one of several methods SQL Server provides for obtaining metadata. Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.
  3. Compatibility Views: Many of the system tables from earlier releases of SQL Server are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. However, the compatibility views do not expose any of the metadata related to features that are introduced in SQL Server 2005 and later. Therefore, when you use new features, such as Service Broker or partitioning, you must switch to using the catalog views.
  4. Replication Views: These views contain information that is used by replication in Microsoft SQL Server. The views enable easier access to data in replication system tables. Views are created in a user database when that database is enabled as a publication or subscription database. All replication objects are removed from user databases when the database is removed from a replication topology. The preferred method for accessing replication metadata is by using Replication Stored Procedures. 
  5. Data-tier Application Views (Transact-SQL): The following views (dbo.sysdac_instances (Transact-SQL), sysdac_instances_internal (Transact-SQL)) display information about data-tier application (DAC) instances. These views are in the msdb database and are owned by the dbo schema.
  6. Dynamic Management Views and FunctionsDynamic 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.

No comments:

Post a Comment