Putting SQL Server catalog views to work

SQL Server catalog views are system views that provide an interface to a wide range of catalog metadata, including information about security objects, full-text search, database mirroring, partitions and more. Read on for an in-depth look at several subcategories of catalog views.

SQL Server 2008 provides hundreds of system views that let you view database and server metadata without having to access the actual source of that data. One category of system views that is particularly useful when retrieving information used by the database engine is the catalog view. SQL Server catalog views are a collection of views that provide a general interface to the catalog metadata.

The amount of information that you can access through catalog views is extensive. For example, use catalog views to retrieve information about the tables and views in a particular database. Or retrieve information about security objects, full-text search, database mirroring, partitions or a variety of other catalog-related information.

SQL Server catalog views can be divided into subcategories along functional lines, such as database object views and security views. In this article, I provide you with examples of views from several of these subcategories. I created the examples on a local instance of SQL Server 2008. In cases in which a specific database needed to be specified, I used the AdventureWorks 2008 database. Be aware, however, that the results I show returned by the sample queries are specific to my instance of SQL Server. If you try out these examples yourself, you’re likely to see different results.

For each catalog view I demonstrate (as well as all catalog views in general), SQL Server provides a help topic specific to that view in SQL Server Books Online. Because of the type of information that each view returns, you’ll want to read the details of each view by accessing that topic.

Database and file catalog views

The first set of SQL Server catalog views we’ll look at let you access information about the databases installed on an instance of SQL Server and the files associated with those databases. In the following example, I use the sys.databases catalog view to retrieve a list of databases whose names begin with the term “adventureworks”.

SELECT
name AS DbName,
database_id AS DatabaseID,
   user_access AS UserAccess,
   user_access_desc AS AccessDescript
FROM
   sys.databases
WHERE
   name LIKE 'adventureworks%';

As the SELECT statement shows, you access a catalog view as you would any other view. In this case, I’ve used the sys.databases view in the FROM clause to retrieve details about an instance’s databases. For a list of columns supported by the view, along with a description of each column, refer to the view’s topic in SQL Server Books Online. The descriptions include explanations of the codes used as values within the columns. For example, the topic explains the codes that are stored in the user_access column, which shows whether the database permits multi-user access (0), single-user access (1) or restricted-user access (2).

The SELECT statement above retrieves the name and ID of each “adventureworks” database, along with the user access code and description of the code. The following table shows the results returned by the query when I run it against my local instance of SQL Server 2008:

DbName

DatabaseID

UserAccess

AccessDescript

AdventureWorks

7

0

MULTI_USER

AdventureWorks2008

8

0

MULTI_USER

AdventureWorksDW2008

10

0

MULTI_USER

So, I’ve installed three “adventureworks” databases on my system. By default, a user access code of 0 is assigned to each one. The description of the user access code is provided in the user_access_desc column, which indicates that each database has a user access of MULTI_USER.

SQL Server also provides a catalog view that lets you retrieve information about database files. In the following SELECT statement, I use the sys.master_files view to access details about the files associated with the AdventureWorks 2008 database:

SELECT
  file_id AS FileID,
  name AS LogicalName,
  type AS FileType,
  type_desc AS TypeDescript
FROM
  sys.master_files
WHERE
  database_id IN
  (
    SELECT database_id
    FROM sys.databases
    WHERE name = 'AdventureWorks2008'
  );

For each file associated with the AdventureWorks 2008 database, I retrieve the file ID, the file’s logical name, the file type code, and the description that explains the type code. Again, reference the topic associated with the sys.master_files catalog view for details about the columns.

Notice that, in order to retrieve the correct data, my WHERE clause includes a subquery that retrieves the database ID from the sys.databases view. This allows me to access the file information without knowing the ID, only the database name.

Once I retrieve the database ID, I can limit my results to information about a specific database. The following results show the information returned about the AdventureWorks 2008 database:

FileID

LogicalName

FileType

TypeDescript

1

AdventureWorks2008_Data

0

ROWS

2

AdventureWorks2008_Log

1

LOG

65537

FileStreamDocuments

2

FILESTREAM

Another SQL Server catalog view is sys.database_files. This view is similar to the sys.master_files view, except that it’s specific to a database. In the following example, I first specify a USE statement that targets the AdventureWorks 2008 database, and then I run a SELECT statement that retrieves data from the sys.database_files view:

USE AdventureWorks2008;
GO
SELECT
  file_id AS FileID,
  name AS FIleName,
  type_desc AS TypeDescript,
  state_desc AS StateDescript,
  size AS Size
FROM
  sys.database_files;

In this case, I retrieve the file ID, logical file name, file type, current state, and size. The following results show the information returned by the statement (on my system):

FileID

FileName

TypeDescript

StateDescript

Size

1

AdventureWorks2008_Data

ROWS

ONLINE

24928

2

AdventureWorks2008_Log

LOG

ONLINE

256

65537

FileStreamDocuments

FILESTREAM

ONLINE

0

As the results indicate, there are three files associated with the AdventureWorks 2008 database. By using the sys.database_files catalog view, I’m able to easily access data on each one. Get more details about this and other database views in “Databases and Files Catalog Views (Transact-SQL)” in SQL Server Books Online and then selecting the specific view.

Object Catalog Views

The next subcategory of SQL Server catalog views is object views. As the name suggests, these views return information about a database’s objects. One of the most useful of these views is sys.objects, which returns information about the user-defined, schema-scoped objects within a specified database. For example, in the following SELECT statement, I use the sys.objects view to retrieve a list of unique constraints in the AdventureWorks database:

USE AdventureWorks2008;
GO
SELECT
  a.name AS ObjectName,
  (
    SELECT b.name
    FROM sys.objects b
    WHERE b.object_id = a.parent_object_id
  ) AS ParentTable
FROM
  sys.objects a
WHERE
  a.type = 'UQ';

In the SELECT statement, I retrieve the name of the unique constraints and the name of the parent objects (tables) associated with those constraints. To retrieve the name of the table, I create a subquery that associates the object ID of the parent to a regular object ID. (Both parent and child objects are available through the sys.objects view.)

I also include a WHERE clause that returns only rows whose type value is UQ. The UQ code stands for unique constraint. As the following results indicate, the AdventureWorks 2008 database contains only one unique constraint, which is defined on the document table:

ObjectName

ParentTable

UQ__Document__F73921F730F848ED

Document

Another useful view in the object subcategory is sys.tables, which returns a list of user-defined tables configured in a database. The following SELECT statement uses this view to retrieve a list of tables in the HumanResources schema of the AdventureWorks 2008 database:

USE AdventureWorks2008;
GO
SELECT
  name AS TableName,
  is_replicated AS IsReplicated,
  is_tracked_by_cdc AS IsTracked
FROM
  sys.tables
WHERE
  schema_id IN
  (
    SELECT schema_id
    FROM sys.schemas
    WHERE name = 'HumanResources' 
  );

As you can see, I retrieve the name of the tables along with whether they’re replicated or tracked by Change Data Capture. Notice that in order limit the results to the HumanResources schema I include a subquery in the WHERE clause that retrieves the schema ID based on the schema name. The following table shows the results returned through the sys.tables view:

TableName

IsReplicated

IsTracked

Department

0

0

Employee

0

0

EmployeeDepartmentHistory

0

0

EmployeePayHistory

0

0

JobCandidate

0

0

Shift

0

0

As the results indicate, there are six tables in the HumanResources schema. The is_replicated and is_tracked_by_cdc columns are both configured as bit columns in which 0 means false and 1 means true. That means none of the tables are replicated or tracked by Change Data Capture.

The next example is similar to the last except that I use the sys.procedures view to retrieve a list of procedures contained in the HumanResources schema:

USEAdventureWorks2008;
GO
SELECT
  name AS ProcName,
  type AS ProcType,
  type_desc AS ProcDescript
FROM
  sys.procedures
WHERE
  schema_id IN
  (
    SELECT schema_id
    FROM sys.schemas
    WHERE name = 'HumanResources' 
  );

In this case, I retrieve the name and type of procedure (both the procedure code and description). As before, I include a subquery in the WHERE clause to limit the results to the specified schema. The SELECT statement returns the results shown in the following table:

ProcName

ProcType

ProcDescript

uspUpdateEmployeeHireInfo

P

SQL_STORED_PROCEDURE

uspUpdateEmployeeLogin

P

SQL_STORED_PROCEDURE

uspUpdateEmployeePersonalInfo

P

SQL_STORED_PROCEDURE

As you can see, the results include only SQL stored procedures. However, the sys.procedures view will also return Common Language Runtime stored procedures, extended stored procedures, and replication-filter procedures if any exist in the database.

If you want to retrieve information about columns in tables or views, use the sys.columns view. In the following example, I retrieve the names of the schema, table and columns for each view in the AdventureWorks 2008 database that includes the term “history” within its name:

USEAdventureWorks2008;

GO
SELECT
  s.name AS SchemaName,
  o.name AS ViewName,
  c.name AS ColumnName
FROM
  sys.columns c
  INNER JOIN sys.objects o
    ON c.object_id = o.object_id
  INNER JOIN sys.schemas s
    ON o.schema_id = s.schema_id
WHERE
  o.type = 'V' AND
  o.name LIKE '%history%'
ORDER BY
  s.name, o.name, c.name;

To pull the names of the views and schemas, I join the sys.columns view to the sys.objects and sys.schemas views. I then use the WHERE clause to limit the results to type V objects (views) with the term “history” in the object name. As it turns out, the AdventureWorks 2008 database includes only one view that includes the term “history,” as shown in the following results:

SchemaName

ViewName

ColumnName

HumanResources

vEmployeeDepartmentHistory

BusinessEntityID

HumanResources

vEmployeeDepartmentHistory

Department

HumanResources

vEmployeeDepartmentHistory

EndDate

HumanResources

vEmployeeDepartmentHistory

FirstName

HumanResources

vEmployeeDepartmentHistory

GroupName

HumanResources

vEmployeeDepartmentHistory

LastName

HumanResources

vEmployeeDepartmentHistory

MiddleName

HumanResources

vEmployeeDepartmentHistory

Shift

HumanResources

vEmployeeDepartmentHistory

StartDate

HumanResources

vEmployeeDepartmentHistory

Suffix

HumanResources

vEmployeeDepartmentHistory

Title

SQL Server also provides a catalog view that lets you view the indexes configured in a database. For example, in the following SELECT statement, I use the sys.indexes view to retrieve all the indexes defined on the Person table in the AdventureWorks 2008 database:

USEAdventureWorks2008;
GO
SELECT
  name AS IndexName,
  type_desc AS IndexType,
  is_primary_key AS PrimaryKey,
  is_unique AS UniqueIndex
FROM
  sys.indexes
WHERE
  object_id IN
  (
    SELECT object_id
    FROM sys.objects
    WHERE name = 'person'
  );

In the case, I retrieve and name and type of each index in the Person table, along with whether the index is the primary key or a unique index. To limit the results to the Person table, I include a subquery in the WHERE clause to retrieve the object ID associated with the Person table. The following results show the indexes defined on that table:

IndexName

IndexType

PrimaryKey

UniqueIndex

PK_Person_BusinessEntityID

CLUSTERED

1

1

IX_Person_LastName_FirstName_MiddleName

NONCLUSTERED

0

0

AK_Person_rowguid

NONCLUSTERED

0

1

PXML_Person_AddContact

XML

0

0

PXML_Person_Demographics

XML

0

0

XMLPATH_Person_Demographics

XML

0

0

XMLPROPERTY_Person_Demographics

XML

0

0

XMLVALUE_Person_Demographics

XML

0

0

The examples I’ve shown here are only some of the object catalog views that SQL Server supports. There are also object views related to assembly modules, computed columns, events, identity columns, triggers, synonyms and other object types. For a list of all object views, see the topic “Object Catalog Views (Transact-SQL)” in SQL Server Books Online.

Security Catalog Views

Another important subcategory of catalog views is the security views, which let you view information about database security, server security, encryption, and audits. For example, you can use the sys.database_principals view to retrieve information about each security principal in a database. In the following SELECT statement, I use this view to retrieve the type and name of each principal in the AdventureWorks 2008 database:

USE AdventureWorks2008;
GO
SELECT
  type_desc AS PrincipalType,
  name AS PrincipalName
FROM
  sys.database_principals
ORDER BY
  type_desc, name;

As you can see, this statement is very straightforward. I simply retrieve the type and name and order the results accordingly, as shown in following table:

PrincipalType

PrincipalName

DATABASE_ROLE

db_accessadmin

DATABASE_ROLE

db_backupoperator

DATABASE_ROLE

db_datareader

DATABASE_ROLE

db_datawriter

DATABASE_ROLE

db_ddladmin

DATABASE_ROLE

db_denydatareader

DATABASE_ROLE

db_denydatawriter

DATABASE_ROLE

db_owner

DATABASE_ROLE

db_securityadmin

DATABASE_ROLE

public

SQL_USER

guest

SQL_USER

INFORMATION_SCHEMA

SQL_USER

sys

WINDOWS_USER

dbo

Another security view specific to databases is the sys.database_permissions view. As you would expect, this view returns information about a database’s permissions. For instance, in the following SELECT statement, I use the sys.database_permissions view to retrieve a count of the number of SELECT permissions that have been granted on the AdventureWorks 2008 database:

USEAdventureWorks2008;
GO
SELECT
  class_desc AS ClassName,
  permission_name AS PermissionName,
  state_desc AS StateName,
  COUNT(*) AS NumberOfSelect
FROM
  sys.database_permissions
WHERE
  type = 'SL' AND
  state = 'G'
GROUP BY
  class_desc,
  permission_name,
  state_desc;

In this statement, I group the results by class, permission type, and state in order to arrive at the count. I use the WHERE clause to limit the permission type to SELECT (SL) and the state to GRANT (G). As the following results show, 129 SELECT permissions have been granted on the AdventureWorks 2008 database:

ClassName

PermissionName

StateName

NumberOfSelect

OBJECT_OR_COLUMN

SELECT

GRANT

129

You can also use security views to retrieve information at the server level. For instance, in the following example I use the sys.server_principals view to retrieve the type and name of the principals on my local instance of SQL Server:

SELECT
  type_desc AS PrincipalType,
  name AS PrincipalName
FROM
  sys.server_principals
WHERE
  type <> 'C'
ORDER BY
  type_desc, name;

Notice in this statement that I use the WHERE clause to exclude type C from the results. Type C refers to certificate mapped logins, which I do not want to include. The following results show the information returned by the sys.server_principals view:

PrincipalType

PrincipalName

SERVER_ROLE

bulkadmin

SERVER_ROLE

dbcreator

SERVER_ROLE

diskadmin

SERVER_ROLE

processadmin

SERVER_ROLE

public

SERVER_ROLE

securityadmin

SERVER_ROLE

serveradmin

SERVER_ROLE

setupadmin

SERVER_ROLE

sysadmin

SQL_LOGIN

##MS_PolicyEventProcessingLogin##

SQL_LOGIN

##MS_PolicyTsqlExecutionLogin##

SQL_LOGIN

sa

WINDOWS_LOGIN

BOBE024\Administrator

WINDOWS_LOGIN

NT AUTHORITY\LOCAL SERVICE

WINDOWS_LOGIN

NT AUTHORITY\SYSTEM

You can also view details about server permissions by using the sys.server_permissions view, as I do in the following example:

SELECT
  class_desc AS ClassName,
  permission_name AS PermissionName,
  state_desc AS StateName,
  COUNT(*) AS NumberOfGrant
FROM
  sys.server_permissions
WHERE
  type = 'CO' AND
  state = 'G'
GROUP BY
  class_desc,
  permission_name,
  state_desc;

In this statement, I’m retrieving the number of CONNECT permissions that have been granted on my local instance of SQL Server. As you saw in a previous example, I’m grouping the data by class, permission type, and state in order to arrive at the results, which are shown in the following table:

ClassName

PermissionName

StateName

NumberOfGrant

ENDPOINT

CONNECT

GRANT

4

In addition to the security views I’ve shown you here, SQL Server supports a number of others. See a list of those views in “System Views (Transact-SQL)” in SQL Server Books Online.

Moving Forward

In this article, I’ve introduced you to several subcategories of catalog views, which themselves are a category of system views. The subcategories I’ve demonstrated here are only a portion of those supported by SQL Server. Find a list of all the subcategories in the topic “Catalog Views (Transact-SQL)” in SQL Server Books Online. From there, link to a list of views in each subcategory, and from there to the individual views. As I stated earlier, when working with catalog views, I recommend that you look closely at their associated topics in SQL Server Books Online for specific details about the type of information that each view returns. The more familiar you become with these views, the more valuable a resource they’ll become.

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at  http://www.rhsheldon.com.

This was first published in January 2011

Dig deeper on Microsoft SQL Server 2008 R2

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close