Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 3, 2005

SQL Server 2005 System Tables and Views - Page 2

By Don Schlichting

Catalog Views

New in SQL 2005 are Catalog Views. Microsoft recommends them as the most general interface to the catalog metadata. They are efficient and all user available catalog metadata is exposed. The amount of views is impressive. Best of all, many of the columns returned by Catalog Views are self-describing. Documentation organizes Catalog Views into several different groups:

  • Partition Function Catalog Views
  • Server-wide Configuration Catalog Views
  • Data Spaces and Fulltext Catalog Views
  • Databases and Files Catalog Views
  • CLR Assembly Catalog Views
  • Schemas Catalog View
  • Scalar Types Catalog Views
  • Security Catalog Views
  • Objects Catalog Views
  • Database Mirroring Catalog Views
  • Messages (For Errors) Catalog Views
  • XML Schemas (XML Type System) Catalog Views
  • Service Broker Catalog Views
  • Linked Servers Catalog Views
  • HTTP Endpoints Catalog Views
  • Extended Properties Catalog Views

The views we need to gather table and column information, like the previous example, are grouped under "Objects Catalog Views". This group includes views on tables, columns, indexes, constraints, and triggers to name a few. Our example requires two views, "sys.tables" and "sys.columns." The columns view will need to be joined on the table view as shown below.

FROM sys.tables

FROM sys.columns INNER JOIN sys.tables ON
	sys.tables.object_id = sys.columns.object_id
WHERE sys.tables.name = 'MyTable'

Sys All

There are four views in a Sys_All group. These views contain information about the System Views as well as user created objects. The views are sys.all_columns, sys.all_objects, sys.all_parameters, and sys.all_views.

Dynamic Management Views

The last groups of views are called Dynamic Management views, or DM. They are used to gather statistics stored in memory but not persistent on disk such as thread information, memory usage, and connection details. These offer administrators a fast and reliable method for obtaining performance numbers. For example, to show the statistics for cached queries, execute this DM statement:

FROM sys.dm_exec_query_stats

These DM views will become invaluable for many DBAs.

System Stored Procedures

In addition to the System Views, there are many System Stored Procedures that can be used for administrative purposes. These pre-made procedures return results similar to System Views. They are located under each database, Programmability, Stored Procedures, and System Stored Procedures. They belong to sys schema.

To obtain column information using a System Stored Procedure, execute sp_columns with the following script:

EXEC sys.sp_columns 'MyTable'


For obtaining SQL Sever metadata information, SQL 2005 offers a large variety of pre-made views and procedures. They are easy and fast to implement and usually return information that is far less cryptic than the tools provided in previous versions.

» See All Articles by Columnist Don Schlichting

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM