Catalog views are system views that expose most facets of the server and its databases in a tabular form. In this tutorial, we’ll take a closer look at database objects and how they’re represented in a database’s catalog views.

sys.schemas

The sys.schemas view contains all the schemas in the current database. The schema is a namespace and can also work as a security container, so you can set permissions not only on individual database objects, but on their entire namespaces (schemas) as well.

The primary key of the view is schema_id.

sys.objects

The sys.objects view contains all objects in the database. Objects are not just tables; they can be views, stored procedures, functions, even constraints like defaults, check constraints, foreign key constraints, primary keys, etc.

There are a few columns in sys.objects to keep track of:

• object_id is the primary key – each object has a unique object_id, even if there are multiple objects with the same name, in different schemas.
• type and type_desc tell you what type of object it is.
• parent_object_id points to another object, in case this object is dependent on others. This applies to triggers, constraints.

Here’s how sys.schemas and sys.objects join:

SELECT s.[name], o.*
FROM sys.schemas AS s
INNER JOIN sys.objects AS o ON s.[schema_id]=o.[schema_id];

Alternatives to sys.objects

There are other views that contain the same data as sys.objects, but with a bit more detail (depending on the type of object)

• sys.tables
• sys.procedures
• sys.views
• sys.triggers
• sys.foreign_keys

sys.columns

sys.columns, as the name implies, contains columns for each database object. The following columns in the view are particularly important:

• column_id provides the column order.
• max_length is the length of the column, and applies to char, varchar, etc.
• precision and scale apply to the numeric/decimal datatypes.
• is_nullable.
• is_identity.

sys.columns is joined to sys.objects using the object_id columns.

SELECT s.[name], o.[name], c.[name], c.max_length,
       c.[precision], c.scale, c.is_nullable, c.is_identity
FROM sys.schemas AS s
INNER JOIN sys.objects AS o ON s.[schema_id]=o.[schema_id]
LEFT JOIN sys.columns AS c ON o.[object_id]=c.[object_id];

sys.indexes, sys.index_columns

For tables and materialized views, information on indexes and their columns can be found in the sys.indexes and sys.index_columns views.

The primary key of sys.indexes is (object_id, index_id). The primary key of sys.index_columns is (object_id, index_id, index_column_id). So sys.indexes is joined to sys.objects using object_id and sys.index_columns is joined to sys.indexes using (object_id, index_id) and to sys.columns using (object_id, column_id).

SELECT o.[object_id], s.[name], o.[name], i.[name], i.type_desc,
       c.[name], ic.key_ordinal, ic.is_included_column
FROM sys.schemas AS s
INNER JOIN sys.objects AS o ON s.[schema_id]=o.[schema_id]
INNER JOIN sys.indexes AS i ON o.[object_id]=i.[object_id]
INNER JOIN sys.index_columns AS ic ON
       i.[object_id]=ic.[object_id] AND i.index_id=ic.index_id
INNER JOIN sys.columns AS c ON
       o.[object_id]=c.[object_id] AND ic.column_id=c.column_id;

All clustered indexes have index_id=0. The is_included_column flag is 1 for columns that are not part of the index key, but just included in the index using INCLUDE.

sys.parameters

sys.parameters contains parameters used by functions and procedures. This view is similar to sys.columns. Like sys.columns, it also contains the max_length, precision and scale columns, and it’s also joined to sys.objects using object_id.

sys.types

sys.types contains all the datatypes of a database, both system types and user-defined types (UDT). It joins in like this:

SELECT s.[name], o.[name], c.[name], t.[name], c.max_length,
       c.[precision], c.scale, c.is_nullable, c.is_identity
FROM sys.schemas AS s
INNER JOIN sys.objects AS o ON s.[schema_id]=o.[schema_id]
LEFT JOIN sys.columns AS c ON o.[object_id]=c.[object_id]
LEFT JOIN sys.types AS t ON c.user_type_id=t.user_type_id;

Note the difference between user types and system types. Both sys.columns, sys.parameters and sys.types contain the columns system_type_id and user_type_id. The primary key for sys.types is user_type_id.

sys.sql_modules

The sys.sql_modules view contains the SQL source of programmed objects, such as views, procedures and functions.

The definition column, an nvarchar(max) column, contains the SQL code needed to create each object.

sys.sql_modules is joined to sys.objects using the object_id column, like this:

SELECT s.[name], o.[name], m.[definition]
FROM sys.schemas AS s
INNER JOIN sys.objects AS o ON s.[schema_id]=o.[schema_id]
INNER JOIN sys.sql_modules AS m ON o.[object_id]=m.[object_id];

System functions

There are also functions to translate plain-text schema and object names to IDs and back.

SELECT SCHEMA_ID('Production'),
       OBJECT_ID('Production.ProductInventory');

SELECT SCHEMA_NAME(7), OBJECT_NAME(2099048);

Deprecated catalog views

In SQL Server 2000, a number of now-deprecated views were used. These views still exist for backward compatibility, but don’t rely on them, and plan to migrate your code to use the current catalog views listed above instead.

• dbo.sysobjects
• dbo.syscolumns
• dbo.systypes

More reading

• MSDN on catalog views