SQL Server System and Database Administration : System Views

4/2/2011 3:44:25 PM
System views are virtual tables that expose metadata that relates to many different aspects of SQL Server. Several different types of views target different data needs. SQL Server 2008 offers an extended number of system views and view types that should meet most, if not all, your metadata needs.

The available system views can be shown in the Object Explorer in SSMS. Figure 1 shows the Object Explorer with the System Views node highlighted. Each system view is covered in detail in SQL Server Books Online, which includes descriptions of each column in the view.

Figure 1. System views listed in Object Explorer.

Compatibility Views

Compatibility views were retained in SQL Server 2008 for backward compatibility. Many of the system tables available in SQL Server 2000 and prior versions of SQL Server are now implemented as compatibility views. These views have the same name as the system tables from prior versions and return the same metadata available in SQL Server 2000. They do not contain information that was added after SQL Server 2000.

You can find most of the compatibility views in the Object Explorer by looking for system views that have names starting with sys.sys. For example, sys.syscolumns, sys.syscomments, and sys.sysobjects are all compatibility views. The first part of the name indicates the schema that the object belongs to (in this case, sys). All system objects are part of this sys schema or the INFORMATION_SCHEMA schema. The second part of the name is the view name, which corresponds to the name of a system table in SQL Server 2000.


To see a list of compatibility views, use the index lookup in SQL Server 2008 Books Online and look for sys.sys. The index is placed at the beginning of a list of compatibility views, starting with sys.sysaltfiles. Objects in the list that are compatibility views have the text compatibility view following the object name, so you can easily identify them and get help.

You also can use the new IntelliSense feature available with SQL Server 2008 to obtain information about the compatibility views and other system views. Simply open a query window in SSMS and start typing a SELECT statement. When you start typing the name of the view that you want to select from (for example, sys.) the IntelliSense drop-down appears listing the views that start with the letters sys. You can also determine the columns available from the view by referencing the view or alias for the view in the column selection list. When you enter the period following the view or alias, the IntelliSense drop-down shows you the available columns.

You should transition from the use of compatibility views to the use of other system views, such as catalog views. The scripts that were created in SQL Server 2000 and reference SQL Server 2000 system tables should continue to function in SQL Server 2008, but this capability is strictly for backward compatibility. Table 1 provides a list of SQL Server 2000 system tables and alternative SQL Server 2008 system views you can use instead.

Table 1. SQL Server 2008 Alternatives for SQL Server 2000 System Tables
SQL Server 2000 System TableSQL Server 2008 System ViewView Type
sysaltfilessys.master_filesCatalog view
syscharsetssys.syscharsetsCompatibility view
syscolumnssys.columnsCatalog view
syscommentssys.sql_modulesCatalog view
sysconfiguressys.configurationsCatalog view
sysconstraintssys.check_constraintsCatalog view
 sys.default_constraintsCatalog view
 sys.key_constraintsCatalog view
 sys.foreign_keysCatalog view
syscurconfigssys.configurationsCatalog view
sysdatabasessys.databasesCatalog view
sysdependssys.sql_dependenciesCatalog view
sysdevicessys.backup_devicesCatalog view
sysfilegroupssys.filegroupsCatalog view
sysfilessys.database_filesCatalog view
sysforeignkeyssys.foreign_keysCatalog view
sysfulltextcatalogssys.fulltext_catalogsCatalog view
sysindexessys.indexesCatalog view
 sys.partitionsCatalog view
 sys.allocation_unitsCatalog view
sysindexkeyssys.index_columnsCatalog view
syslanguagessys.syslanguagesCompatibility view
sysloginssys.sql_logins (transact-sql)Catalog view
sysmemberssys.database_role_membersCatalog view
sysmessagessys.messagesCatalog view
sysobjectssys.objectsCatalog view
sysoledbuserssys.linked_loginsCatalog view
syspermissionssys.database_permissionsCatalog view
 sys.server_permissionsCatalog view
sysprotectssys.database_permissionsCatalog view
 sys.server_permissionsCatalog view
sysreferencessys.foreign_keysCatalog view
sysremoteloginssys.remote_loginsCatalog view
sysserverssys.serversCatalog view
systypessys.typesCatalog view
sysuserssys.database_principalsCatalog view


Catalog Views

Using catalog views is the preferred method for returning information used by the Microsoft SQL Server database engine. There is a catalog view to return information about almost every aspect of SQL Server. The number of catalog views is far too large to list here, but you can gain some insight into the range of information available by looking at the following list, which shows the categories of information covered by catalog views:

  • Change Tracking

  • Common language runtime (CLR) assembly

  • Data spaces and full text

  • Database mirroring

  • Data spaces

  • Endpoint

  • Extended properties

  • Linked servers

  • Messages (for errors)

  • Objects

  • Partition function

  • Resource Governor

  • Scalar types

  • Schemas

  • Security

  • Server-wide configuration

  • Service Broker

  • SQL Server Extended Events

  • XML schemas (XML type system)

Some of the catalog views return information that is new to SQL Server 2008. Examples include the Change Tracking and Resource Governor catalog views. Other catalog views provide information that may have been available in prior versions through system tables, system procedures, and so on, but the new catalog views expand on the information returned and include elements that are new to SQL Server 2008.

To demonstrate the use of a catalog view, let’s compare a simple SQL Server 2000 SELECT statement that returns object information to a SELECT statement in SQL Server 2008 that returns similar information. The following example shows a SELECT statement written in SQL Server 2000 to return any stored procedure created after a given date:

select o.crdate,
from sysobjects o
where type = 'p'
and crdate > '1/1/08'
order by crdate, name

Now, compare this SELECT statement to one that uses a SQL Server 2008 catalog view. The sys.objects catalog view is a new alternative to the SQL Server 2000 sysobjects system table. The following SELECT uses the sys.objects catalog view to return the same type of information as the preceding example:

select o.create_date, o.modify_date, name
from sys.objects o
where type = 'p'
and (create_date > '1/1/08'
or o.modify_date >= '1/1/08')
order by 1, 2, 3

As you can see, the modify_date column has been added to the SELECT statement. This column did not exist with the sysobjects system table. The addition of this column allows you to identify objects that were created as well as objects that were modified or altered.

Let’s look at an example of using a catalog view to return the same kind of information returned in SQL Server 2000 with a system procedure. The handy sp_helpfile system procedure returns information about database files associated with a given database. This SQL Server 2000 procedure is still available in SQL Server 2008. An alternative to this procedure is the new sys.master_files catalog view. This view returns all the information that sp_helpfile returns and more. The following example shows a SELECT statement using the sys.master_files catalog view to return the database files for the AdventureWorks2008R2 database:

select *
from sys.master_files
where db_name(database_id) = 'AdventureWorks2008R2'

You have the distinct advantage of being able to select the database files for all the databases on your server by using this catalog view. You can also tailor your SELECT statement to isolate database files based on the size of the database or the location of the physical database files. For example, to return all database files that are found somewhere on your C drive, you could use the following SELECT:

select db_name(database_id), physical_name
from sys.master_files
where physical_name like 'c:\%'

There are plenty of catalog views that provide information about SQL Server. When you are looking to return information about SQL Server components, you should look to the catalog views first. These views provide a great deal of flexibility and allow you to isolate the specific information you need.

Information Schema Views

Information schema views provide another system table–independent option for accessing SQL Server metadata. This type of view was available in prior versions of SQL Server. Using information schema views is a viable alternative for accessing SQL Server metadata from a production application. The information schema views enable an application that uses them to function properly even though the underlying system tables may have changed. Changes to the underlying system tables are most prevalent when a new version of SQL Server is released (such as SQL Server 2008), but changes can also occur as part of service packs to an existing version.

The information schema views also have the advantage of being SQL-92 compatible. Compliance with the SQL-92 standard means that SQL statements written against these views work with other DBMSs that also adhere to the SQL-92 standard. The SQL-92 standard supports a three-part naming convention, which SQL Server has implemented as database.schema.object.

In SQL Server 2008, all the information schema views are in the same schema, named INFORMATION_SCHEMA. The following information schema views or objects are available:





















When you refer to information schema views in a SQL statement, you must use a qualified name that includes the schema name. For example, the following statement returns all the tables and columns in a given database, using the tables and columns information schema views:



You can expand the Views node in a given database in the Object Explorer and open the System Views node to see a list of the available information schema views. The information schema views are listed at the top of the System Views node. If you expand the Column node under each information schema view, you see the available columns to select from the view. You can then drag the column into a query window for use in a SELECT statement. You can also use IntelliSense in a query window determine the columns.

Fortunately, the names of the information schema views are fairly intuitive and reflect the kind of information they contain. The relationships between the information schema views can be derived from the column names shared between the tables.

Dynamic Management Views

Dynamic management views (DMVs), which were introduced in SQL Server 2005, provide a simple means for assessing the state of a server. These views provide a lightweight means for gathering diagnostic information without the heavy burden associated with the tools available in SQL Server 2000. The SQL Server 2000 diagnostic tools, such as heavy Profiler traces, PerfMon, dbcc executions, and pssdiag, are still available, but oftentimes, the information returned from the DMVs is enough to determine what may be ailing a SQL Server machine.

An extensive number of DMVs are available in SQL Server 2008. Some DMVs are scoped at the server level, and others are scoped at the database level. They are all found in the sys schema and have names that start with dm_. Table 2 lists the different types of DMVs. The DMVs in this table are categorized based on function as well as the starting characters in the DMV names. The naming convention gives you an easy means for identifying the type of each DMV.

Table 2. Types of DMVs
CategoryName PrefixInformation Captured
Auditingdm_auditNew Auditing information
Service Brokerdm_brokerServer Broker statistics, including activated tasks and connections
Change Datadm_cdcNew Change Data Capture information
CLRdm_clrCLR information, including the CLR loaded assemblies
Cryptographicdm_crypSecurity related data
TDEdm_databaseTransparent Data Encryption
Databasedm_dbDatabases and database objects
Executiondm_execExecution of user code
Full-Textdm_ftsFull-Text Search information
I/Odm_ioInput and output on network disks
Operating systemdm_osLow-level operating system information, including memory and locking information
Providerdm_providerExtensible Key Management (EKM)
Query Notificationdm_qnActive Query Notification subscriptions
Replicationdm_replReplication information, including the articles, publications, and transaction involved in replication
Serverdm_serverServer Audit status
Transactiondm_tranTransactions and isolation-level information
Objectdm_sqlObject References
Extended Eventsdm_xeNew event handling infrastructure


You can expand the Views node in a given database in the Object Explorer and open the System Views node to see a list of the available DMVs. The DMVs are all listed together and start with dm_. If you expand the Column node under each DMV, you see the available columns to select from the view. You can then drag the column into a query window to be included in a SELECT statement.

To illustrate the value of the DMVs, let’s look at a performance scenario and compare the SQL Server 2000 approach to a SQL Server 2008 approach using DMVs. A common performance-related question is “What stored procedures are executing most frequently on my server?” With SQL Server 2000, the most likely way to find out is to run a Profiler trace. You must have a Profiler trace that has already been running to capture the stored procedure executions, or you must create a new trace and run it for a period of time to answer the performance question. The trace takes time to create and can affect server performance while it is running.

With SQL Server 2008, you can use one of the DMVs in the execution category to answer the same performance question. The following example uses the sys.dm_exec_query_stats DMV along with a dynamic management function named dm_exec_sql_text. It returns the object IDs of the five most frequently executed stored procedures, along with the actual text associated with the procedure:

select top 5 q.execution_count, q.total_worker_time,
s.dbid, s.objectid, s.text
from sys.dm_exec_query_stats q
CROSS APPLY sys.dm_exec_sql_text (q.sql_handle) s
ORDER BY q.execution_count desc

The advantage of using a DMV is that it can return past information without having to explicitly create a trace or implement some other performance tool. SQL Server automatically caches the information so that you can query it at any time. The collection of the data starts when the SQL Server instance is started, so you can get a good cross-section of information. Keep in mind that your results can change as the server continues to collect information over time.

Many of the performance scenarios such as those that relate to memory, CPU utilization, blocking, and recompilation can be investigated using DMVs. You should consider using DMVs to address performance problems before using other methods in SQL Server 2008. In many cases, you may be able to avoid costly traces and glean enough information from the DMV to solve your problem.


Dynamic management functions return the same type of information as DMVs. The dynamic management functions also have names that start with dm_ and reside in the sys schema. You can find the dynamic management functions listed in the Object Explorer within the master database. If you select Function, System Functions, Table-Valued Functions, you see the dynamic management functions listed at the top.

DMVs are also a great source of information that does not relate directly to performance. For example, you can use the dm_os_sys_info DMV to gather important server information, such as the number of CPUs, the amount of memory, and so on. The following example demonstrates the use of the dm_os_sys_info DMV to return CPU and memory information:

select cpu_count, hyperthread_ratio, physical_memory_in_bytes
from sys.dm_os_sys_info

/* Results from prior select

cpu_count hyperthread_ratio physical_memory_in_bytes
----------- ----------------- ------------------------
2 2 2146357248

The cpu_count column returns the number of logical CPUs, hyperthread_ratio returns the ratio between physical CPUs and logical CPUs, and the last column selected returns the physical memory on the SQL Server machine.

  •  SQL Server System and Database Administration : System Tables & System Stored Procedures
  •  SQL Server System and Database Administration : System Databases
  •  SQL Server 2008 : Monitoring Your Server - Monitoring Your CPU
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 3) - XML DML & Converting a Column to XML
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 2) - SQL Server 2005 XQuery in Action
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 1) - XQuery Defined
  •  SQL Server 2008 : Monitoring Your Server - Familiarizing Yourself with the Performance Monitor
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 3) - OPENXML Enhancements in SQL Server 2005
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 2) - FOR XML EXPLICIT
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 1) - FOR XML RAW & FOR XML AUTO
    Top 10 Video Game
    -   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Total War: Warhammer [PC] Demigryph Trailer
    -   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
    -   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
    -   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
    -   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
    -   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
    -   Satellite Reign [PC] Release Date Trailer