:::: MENU ::::

System Views in SQL Server 2005 for metadata

  • Nov 05 / 2008
  • 1
dbDigger, Dynamic Management Views DMV, Monitoring and Analysis

System Views in SQL Server 2005 for metadata

System views are new for SQL Server 2005. System views in SQL Server 2005 are designed to expose instance and database related metadata in an organized and useful method. Some system tables from SQL Server 2000 are now implemented in SQL Server 2005 as system views for backward compatibility. Although the SQL Server 2000 system tables can still be queried by name, SQL Server 2005 features and related metadata will not be seen. Thus, the results may be different from those when querying the corresponding new system view.
There are many SQL Server 2005 system tables that do not have system views for accessing data from them, such as the backup and restore history tables. In those cases, the data must be accessed by querying the system table itself.
System base tables are the underlying tables that actually store metadata for a specific database. System base tables are used within SQL Server 2005 Database Engine and are not intended for customer user. Therefore, the system views are provided for accessing that metadata without accessing the base tables.
All of the system objects referenced by the system views are physically persisted in the system base tables stored within the read only system database called Resource. This database is not visible to users and does not appear in SQL Server Management Studio. Users cannot use or connect to it, unless in single-user mode.
All system views are contained in either the INFORMATION_SCHEMA or the sys schemas. Both schemas logically appear in every database.
There are six collections of system views:
  • catalog
  • compatibility
  • dynamic management
  • information schema
  • replication
  • notification services
There are numerous categories of system views within each collection. Following are descriptions of each of the collections:

Catalog views
Catalog views return information that is used by the Database Engine, such as information on objects, databases, files, security, and more. They do not contain information about backups, replication, database maintenance plans, or SQL Server Agent catalog data.

Compatibility views
Compatibility views are provided for backward compatibility only with SQL Server 2000 system tables. They do not expose any SQL Server 2005 new feature metadata, such as partitioning. Use the new catalog views instead.

Dynamic management views (DMVs)
Dynamic management views (DMVs) return information on server state or database state that can be used for monitoring the health of a server instance and databases, and diagnose performance problems. Dynamic management views (DMVs) can be identified by their name, which begins with ”dm_,” plus an abbreviation of what category the DMV is a part, then a description of what the view returns. For example, dm_db_file_space_usage is part of the database category of DMVs and returns information on file space usage.

Information schema views
Information schema views are system views that are part of a separate schema, called INFORMATION_SCHEMA. Returns metadata for database objects in a particular database. All other system views are part of the sys schema.

Replication views
Replication views return information about replication. They are created when a database is configured as a publisher or subscriber, and different views are created in the different databases: msdb, distribution, publisher database, and subscriber database. Otherwise, these views will not exist. Using replication stored procedures is still a good way to access replication metadata.

Notification services views
Notification services views return instance and application data specifically related to Notification Services; designed to help with debugging, tracking, or troubleshooting.

  • Anonymous

    I liked this broad overview of introduced views in SQL Server 2005.

Consult us to explore the Databases. Contact us