SQL Server System Views: The Basics

Comments 4

Share to social media

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.

About the author

Robert Sheldon

See Profile

Robert is a freelance technology writer based in the Pacific Northwest. He’s worked as a technical consultant and has written hundreds of articles about technology for both print and online publications, with topics ranging from predictive analytics to 5D storage to the dark web. He’s also contributed to over a dozen books on technology, developed courseware for Microsoft’s training program, and served as a developmental editor on Microsoft certification exams. When not writing about technology, he’s working on a novel or venturing out into the spectacular Northwest woods.

Robert's contributions