SQL Server provides an assortment of system views for accessing metadata about the server environment and its database objects. There are catalog views and information schema views and dynamic management views and several other types of views. DBAs and developers alike can benefit significantly from the rich assortment of information they can derive through these views, and it is worth the effort to get to know them.

System views are divided into categories that each serve a specific purpose. The most extensive category is the one that contains catalog views. Catalog views let you retrieve information about a wide range of system and database components-from table columns and data types to server-wide configurations.

Information schema views are similar to some of the catalog views in that they provide access to metadata that describes database objects such as tables, columns, domains, and check constraints. However, information schema views conform to the ANSI standard, whereas catalog views are specific to SQL Server.

In contrast to either of these types of views, dynamic management views return server state data that can be used to monitor and fine-tune a SQL Server instance and its databases. Like catalog views, dynamic management views are specific to SQL Server.

In this article, we’ll focus on these three types of views, looking at examples in each category. We won’t be covering the other types of system views because they tend not to be as commonly used, with perhaps a couple exceptions. For the most part, catalog, information schema, and dynamic management views are the ones you’ll likely be using the most often. But just so you know, the other types are related to replication and data-tier application (DAC) instances as well as provide compatibility with earlier SQL Server releases. Although they have their places, for now we’ll stick with the big three.

Catalog views

Of the various types of system views available in SQL Server, catalog views represent the largest collection and most diverse. You can use catalog views to gather information about such components as AlwaysOn Availability Groups, Change Data Capture, change tracking, database mirroring, full-text search, Resource Governor, security, Service Broker, and an assortment of other features-all in addition to being able to view information about the database objects themselves.

In fact, SQL Server provides so many catalog views that it would be nearly impossible-or at least highly impractical-to try look at all of them in one article, but know that there is a vast storehouse of views waiting for you, and they all work pretty much the same way.

Microsoft recommends that you use catalog views as your primary method for accessing SQL Server metadata because they provide the most efficient mechanism for retrieving this type of information. Through the catalog views you can access all user-available metadata. For example, the following SELECT statement returns information about databases whose name starts with adventureworks:

The columns specified in the SELECT clause-name, database_id, and compatibility_level-represent only a fraction of the many columns supported by this view. The view will actually return nearly 75 columns worth of information about each database installed on the SQL Server instance. I’ve kept it short for the sake of brevity, as shown in the following results:

name	database_id	compatibility_level
AdventureWorks2014	9	120
AdventureWorksDW2014	10	120

There is nothing remarkable here, except for the ease with which I was able to collect the metadata. The results include the database names, their auto-generated IDs, and their compatibility levels, which in both cases is 120. The 120 refers to SQL Server 2014. (I created the examples in this article on a local instance of SQL Server 2014 running in a test virtual machine.)

The sys.databases view can also return information about database settings, such as whether the database is read-only or whether the auto-shrink feature is enabled. Many of the configuration-related columns take the bit data type to indicate whether a feature is on (1) or off (0).

As the preceding example illustrates, you access catalog views through the sys schema. Whichever view you use, it’s always a good idea to check the SQL Server documentation if you have any questions about its application to your particular circumstances. For example, the sys.databases view includes the state column, which provides status information such as whether a database is online, offline, or being restored. Each option is represented by one of nine predefined tinyint values. Some values in this column pertain only to certain environments. For instance, the value 7 (copying) applies only to Azure SQL Database.

Now let’s look at the sys.objects catalog view, which returns a row for each user-defined, schema-scoped object in a database. The following SELECT statement retrieves the name and ID of all table-valued functions defined in the dbo schema within the AdventureWorks2014 sample database:

Notice that I use the SCHEMA_NAME built-in function to match the schema ID to dbo in the WHERE clause. Functions such as SCHEMA_NAME, OBJECT_ID, OBJECT_NAME, and so on can be extremely useful when working with catalog views.

Also in the WHERE clause, I match the type_desc column to sql_table_valued_function, giving me the following results:

name	object_id
ufnGetContactInformation	103671417

The sys.objects view is a handy tool to have because it provides quick and easy access to all user-defined objects in your database, including tables, views, triggers, functions, and constraints. However, SQL Server also provides catalog views that are distinct to a specific object type. For example, the following SELECT statement retrieves data through the sys.tables view:

The statement returns a list of all tables in the HumanResources schema, along with the maximum column ID used for each table, as shown in the following results:

name	max_column_id_used
Shift	5
Department	4
Employee	16
EmployeeDepartmentHistory	6
EmployeePayHistory	5
JobCandidate	4

The interesting thing about the sys.tables view is that it inherits all the columns from the sys.objects view and then adds additional columns with table-specific information. For example, in the preceding example, the name column is inherited from sys.objects but the max_column_id_used column is specific to sys.tables. (For information about which views inherit columns from other views, refer to the SQL Server documentation.)

You can also join catalog views to retrieve specific types of information. For example, the following SELECT statement joins the sys.columns view to the sys.types view to retrieve information about the Person table:

Not surprisingly, the sys.columns view returns a list of columns in the table, and the sys.types view returns the name of the column data types, along with whether they are system types or user-defined:

ColumnName	DataType	UserOrSystem
BusinessEntityID	int	system type
PersonType	nchar	system type
NameStyle	NameStyle	user-defined type
Title	nvarchar	system type
FirstName	Name	user-defined type
MiddleName	Name	user-defined type
LastName	Name	user-defined type
Suffix	nvarchar	system type
EmailPromotion	int	system type
AdditionalContactInfo	xml	system type
Demographics	xml	system type
rowguid	uniqueidentifier	system type
ModifiedDate	datetime	system type

Up to this point, the catalog views we’ve looked at have focused on the databases and their objects. However, we can use catalog views to retrieve all sorts of information, such as details about database files:

In this case, we’re using the sys.database_files view to retrieve the file ID, file name, file state, and file type.

file_id	name	state_desc	type_desc
1	AdventureWorks2014_Data	ONLINE	ROWS
2	AdventureWorks2014_Log	ONLINE	LOG

We might instead use the sys.assembly_types view to return information about any assemblies added to the database:

As the following results show, the AdventureWorks2014 database includes three assemblies, all of which are SQL Server’s advanced data types:

name	user_type_id	assembly_class
hierarchyid	128	Microsoft.SqlServer.Types.SqlHierarchyId
geometry	129	Microsoft.SqlServer.Types.SqlGeometry
geography	130	Microsoft.SqlServer.Types.SqlGeography

You can even retrieve data about security-related metadata within your database. For example, the following SELECT statement uses the sys.database_principals view to return the names and IDs of all security principals in the AdcentureWorks2014 database:

Notice that we’ve used a WHERE clause to qualify our query so the SELECT statement returns only the DATABASE_ROLE principal type:

name	principal_id
public	0
db_owner	16384
db_accessadmin	16385
db_securityadmin	16386
db_ddladmin	16387
db_backupoperator	16389
db_datareader	16390
db_datawriter	16391
db_denydatareader	16392
db_denydatawriter	16393

Of course, SQL Server security occurs at the database level and at the server level. To address the server level, SQL Server also includes catalog views specific to the current instance. For example, the following SELECT statement joins the sys.server_principals view and the sys.server_permissions view to retrieve information about the server principals and their permissions:

In this case, we’re concerned only with the SERVER_ROLE principal type, so we’ve added the WHERE clause, giving us the following results:

name	principal_id	oermission_name	state_desc
public	2	VIEW ANY DATABASE	GRANT
public	2	CONNECT	GRANT
public	2	CONNECT	GRANT
public	2	CONNECT	GRANT
public	2	CONNECT	GRANT

You can also use catalog views to retrieve server configuration information. For instance, the following SELECT statement uses the the sys.configurations view to retrieve configuration information about the current server:

In this case, we’ve limited our query to non-dynamic advanced settings, as shown in the following results:

name	description
user connections	Number of user connections allowed
locks	Number of locks for all users
open objects	Number of open database objects
fill factor (%)	Default fill factor percentage
c2 audit mode	c2 audit mode
priority boost	Priority boost
set working set size	set working set size
lightweight pooling	User mode scheduler uses lightweight pooling
scan for startup procs	scan for startup stored procedures
affinity I/O mask	affinity I/O mask
affinity64 I/O mask	affinity64 I/O mask
common criteria compliance enabled	Common Criteria compliance mode enabled

There are, of course, many more examples of catalog views I can show you, but you get the point. There’s a great deal of information to be had, and I’ve barely scratched the surface. For a complete listing of the available catalog views, check out the MSDN topic Catalog Views (Transact-SQL).

Information schema views

Information schema views provide a standardized method for querying metadata about objects within a database. The views are part of the schema INFORMATION_SCHEMA, rather than the sys schema, and are much more limited in scope than catalog views. At last count, SQL Server was providing only 21 information schema views, compared to over 200 catalog views.

The advantage of using information schema views is that, because they are ANSI-compliant, you can theoretically migrate your code to different database systems without having to update your view references. If portability is important to your solution, you should consider information schema views, just know that they don’t do nearly as much as catalog views. And, of course, using one type of view doesn’t preclude you from using another type of view.

With information schema views, you can retrieve metadata about database objects such as tables, constraints, columns, privileges, views, and domains. (In the world of information schema views, a domain is a user-defined data type, and a catalog is the database itself.)

Let’s look at a few examples. The first one uses the TABLES view to retrieve the name and type of all the tables and views in the Purchasing schema:

No magic here. Just a simple query that returns basic information, as shown in the following results:

TABLE_NAME	TABLE_TYPE
ProductVendor	BASE TABLE
PurchaseOrderDetail	BASE TABLE
PurchaseOrderHeader	BASE TABLE
ShipMethod	BASE TABLE
Vendor	BASE TABLE
vVendorWithAddresses	VIEW
vVendorWithContacts	VIEW

We could have also retrieved the TABLE_CATALOG and TABLE_SCHEMA columns, which are included in the view to provide fully qualified, four-part names for each object, but we didn’t need that information in this case, and the table includes no other columns, falling far short of what you get with sys.tables.

Now let’s pull data through the COLUMNS view, which provides a few more details than we get with TABLES:

In this case, our query retrieves the column name, system data type, and user data type, if any, within the Person.ContactType table. In this case, the table includes only one user-defined data type (Name):

COLUMN_NAME	DATA_TYPE	DOMAIN_NAME
ContactTypeID	int	NULL
Name	nvarchar	Name
ModifiedDate	datetime	NULL

Now suppose we want to retrieve a list of user-defined data types in the AdventureWorks2014 database, along with the base type for each one:

This time, we use the DOMAINS view, which gives us the following results (at least on my system):

DOMAIN_NAME	DATA_TYPE
AccountNumber	nvarchar
Flag	bit
Name	nvarchar
NameStyle	bit
OrderNumber	nvarchar
Phone	nvarchar

Let’s look at one more example, this one of the CHECK_CONSTRAINTS view, which retrieves information about the check constraints in the Person schema:

In this case, we get the name of the check constraints, along with the constraint definitions:

CONSTRAINT_NAME	CHECK_CLAUSE
CK_Person_EmailPromotion	([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))
CK_Person_PersonType	([PersonType] IS NULL OR (upper([PersonType])=’GC’ OR upper([PersonType])=’SP’ OR upper([PersonType])=’EM’ OR upper([PersonType])=’IN’ OR upper([PersonType])=’VC’ OR upper([PersonType])=’SC’))

That’s all there is to information schema views. There are relatively few of them and the ones that are there contain relatively little information, when compared to their catalog counterparts. You can find more details about information schema views by referring to the MSDN topic Information Schema Views (Transact-SQL).

Dynamic management views

With dynamic management views, we move into new territory. The views return server state information about your databases and servers, which can be useful for monitoring your systems, tuning performance, and diagnosing any issues that might arise.

Like catalog views, dynamic management views provide a wide range of information. For example, SQL Server includes a set of dynamic management that are specific to memory-optimized tables. One of these, dm_xtp_system_memory_consumers, returns information about database-level memory consumers:

The statement retrieves the consumer description, the amount of allocated bytes, and the amount of used bytes for the pgpool consumer type, giving us the following results.

memory_consumer_desc	allocated_bytes	used_bytes
System 256K page pool	262144	262144
System 64K page pool	0	0
System 4K page pool	0	0

Like catalog views, dynamic management views are part of the sys schema. In addition, their names always begin with the dm_ prefix. Unfortunately, Microsoft uses the same naming convention for SQL Server’s dynamic management functions. But you’ll quickly discover which ones are which when you try to run them and you’re prompted to provide input parameters. (I’ll save a discussion about the functions for a different article.)

Another category of dynamic management views focuses on the SQL Server Operating System (SQLOS), which manages the operating system resources specific to SQL Server. For example, you can use the dm_os_threads view to retrieve a list of SQLOS threads running under the current SQL Server process:

The statement returns the thread ID, kernel time, and user time, for those threads greater than 300 milliseconds, giving us the following results (on my test system):

os_thread_id	kernel_time	usermode_time
2872	140	327
2928	15	1014
2944	46	327
5500	78	1216

The SQLOS views even include one that returns miscellaneous information about the computer and its resources:

Although the dm_os_sys_info view can return a variety of information about the environment, in this case, we’ve limited that information to the CPU count, physical memory, and virtual memory:

cpu_count	physical_memory_kb	virtual_memory_kb
4	4193840	8589934464

SQL Server also includes dynamic management views for retrieving information about the indexes. For example, you can use the db_index_usage_stats view to return details about different types of index operations:

The statement returns the data shown in the following table:

index_id	user_seeks	user_scans
1	4	9

Being able to query statistics about an index in this way can be useful when testing an application’s individual operations. This can help you pinpoint whether your queries are using the indexes effectively or whether you might need to build different indexes. Note, however, that index statistics can reflect all activity, whether generated by an application or generated internally by SQL Server.

Dynamic management views are either server-scoped or database-scoped. The ones we’ve look at so far have been server-scoped, even the dm_db_index_usage_stats index shown in the last example. In that case, however, we were concerned with only the AdventureWorks2014 database, so we specified the database in our WHERE clause.

If you want to run a database-scoped dynamic management view, you must do so within the context of the target database. In the following SELECT statement, I use the dm_db_file_space_usage view to return space usage data about the data file used by the AdventureWorks2014 database:

All I’m doing here is retrieving the total page count, allocated extent page count, and unallocated extent page count:

total_page_count	allocated_extent_page_count	unallocated_extent_page_count
30368	28368	2000

Note that page counts are always at the extent level, which means they will be multiples of eight.

We can instead use the dm_db_fts_index_physical_stats view to retrieve data about the full-text and sematic indexes in each table:

This time we get the object name and ID of the table that contains the index, as well as the page count for each index:

ObjectName	ObjectID	IndexPages
ProductReview	610101214	8
Document	1077578877	13
JobCandidate	1589580701	15

Let’s look at one more dynamic management view that is database-scoped. The dm_db_persisted_sku_features view returns a list of edition-specific features that are enabled in the current database, but are not supported on all SQL Server versions. The view applies to SQL Server 2008 through the current version. The following SELECT statement uses the view to retrieve the feature name and ID:

In this case, the SELECT statement returns only one row:

feature_name	feature_id
InMemoryOLTP	800

 The dm_db_persisted_sku_features view includes the feature_id column only for informational purposes. The column is not supported and may not be part of the view in the future.

Although these are but a few of the dynamic management views that SQL Server supports, the examples should give you a good sense of the variety of data they can provide. For a complete list of dynamic management views and to learn more about each one, refer to the MSDN topic Dynamic Management Views and Functions (Transact-SQL).

Plenty more where that came from

As mentioned earlier, SQL Server also provides system views to support backward compatibility, replication, and DAC instances. The compatibility views might come in handy if you’re still running SQL Server 2000. You might also find the replication-related views useful if you’ve implemented replication, although Microsoft recommends that you instead use the stored procedures available for accessing replication metadata. As for the DAC views, SQL Server provides only two of them, and they reside only in the msdb database.

For many DBAs and database developers, the catalog views and dynamic management views will likely be their first line of defense when retrieving SQL Server metadata, whether it’s specific to particular database objects or the server environment as a whole. That’s not to diminish the importance of the other views, but rather to point out that Microsoft has put most of its effort into building an extensive set of catalog views and dynamic management views. And given all the work that’s gone into them, there’s certainly no reason not to take advantage of what’s available.