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.