SQL Metadata in sys.databases, sys.objects, sys.tables and sys.columns

By: Rick Dobson   |   Updated: 2024-08-28   |   Comments   |   Related: More > Database Administration

Problem

Metadata is data about data. On a SQL Server instance, metadata can reside in nested containers. The outermost container is a SQL Server instance. Within a SQL Server instance, data can be organized in databases. Some databases are system-created databases, and other databases are user-created databases. What are valuable SQL Server metadata queries for databases, database objects, tables, columns, and keys?

Solution

This tip, the first in a two-part series on SQL Server metadata objects, aims to help the reader understand the structure and use cases for sys schema catalog views. The T-SQL examples in this tip will give you exposure to system catalog views, such as:

• sys.databases is a built-in SQL Server catalog view that itemizes the databases on a SQL Server instance. Columns within each sys.database's row designate the name and other properties of each database on a SQL Server instance.
• sys.objects is a built-in SQL Server catalog view that itemizes user-defined and system-defined objects and their relationships within each database on a SQL Server instance.
• sys.tables is a built-in SQL Server catalog view that itemizes the user-defined tables residing within a SQL Server database engine. The view contains a row for each table created by a user within a database.
• sys.columns is a built-in catalog view that can track the columns within each table within each database of a SQL Server instance. There is a row in sys.columns for columns from both system-created and user-created table objects within a database.

Sys schema catalog views were initially introduced with the 2005 version of SQL Server. Subsequent SQL Server versions are updated to accommodate new metadata features. Here are some prior MSSQLTips.com articles on sys schema catalog views:

• "Starter Metadata T-SQL Query Examples for Databases and Tables"
• "SQL Server Code Samples for Sys.objects and Related Functions"
• "Over 40 queries to find SQL Server tables with or without a certain property"

Information_schema catalog views offer another standard for querying metadata. The information_schema catalog view standard was introduced before the SQL Server sys schema catalog view. The information_schema catalog view is explicitly designed to query metadata from any database subscribing to the SQL-92 standard. In contrast, sys schema catalog views usually operate faster on SQL Server instances and offer more extensive access to the full range of SQL Server metadata features.

Use Cases for sys.databases

As mentioned previously, the sys.databases catalog view focuses exclusively on the databases within a SQL Server instance. The following script returns a list of the databases on a SQL Server instance. Each row in the result set reveals the name, database_id of a database on the current SQL Server instance, and the datetime value of its creation.

• Database names within a SQL Server instance must be unique. Database names have a sysname datatype, which is equivalent to a nvarchar (128) datatype value.
• The database_id values have an integer data type value.
• The create_date value has a datetime data type value.

-- list database name, database_id, and create_date for databases
-- on a sql server instance
select 
 name [database name]
,database_id
,create_date
from sys.databases

The first four rows in the following listing display the database name, database_id, and create_date column values for a SQL Server 2019 instance. These databases are server-generated and are derived from SQL Server installation files. The model database is initially server-generated but can be updated after installation. Additional databases can be created depending on your database configuration choices during and after installation. MSSQLTips.com offers an additional tip with an introduction to selected system-generated databases.

The remaining seven rows in the following listing display database name, database_id, and create_date column values for user-generated databases. These values reflect the needs of individual database creators.

Examples of selected other sys.databases columns include:

• Recovery model description (for example: full, simple, and bulk-logged),
• state_desc (online, offline, restoring, and other related options), and
• compatibility_level, which can range in value from 65 for SQL Server 6.5 through 160 for SQL Server 2022.

Use Case and Row Examples for sys.objects

Each object within a SQL Server instance has a unique object_id value. The sys.objects catalog view permits DBAs \ Developers to access SQL Server metadata for:

• The full collection of objects on a SQL Server instance,
• The objects within a database, or
• A filtered segment of the objects in a database.

The T-SQL examples in this section illustrate all three approaches and review the types of metadata that are available for the SQL Server objects. These metadata items are denoted by the columns of the sys.objects catalog view.

The following script selects three columns from the sys.objects catalog view within the currently active schema in the currently active database. The name column indicates the name value from within each row of sys.objects. The object_id column values in sys.objects contain unique integer identifiers for each object within the currently active schema. The type_desc column reflects the type of value for an object in sys.objects. Typical kinds of type_desc values include user_table, primary_key_constraint, and sql_stored_procedure.

-- name, object_id, and type_desc values
-- excerpted from the sys.objects catalog view
-- of the current database within the current SQL Server Instance
select name, object_id, type_desc 
from sys.objects
order by type_desc

The following screenshot displays the first 23 rows for the result set from the preceding query. The first 22 rows are for objects with an internal_table type. The 23rd row is for an object that points at a SQL Server primary_key_constraint.

The next script contains two queries for metadata from a sys.objects catalog view:

• The use statement towards the top of the script designates DataScience as the active database from which to extract metadata rows. You can designate any other database about which you seek metadata.
• The first query lists all the columns from the sys.objects catalog view. The where clause in the last line of the first query restricts its output to those with a type_desc value of 'primary_key_constraint.'
• The second query takes a subset of the columns from the full set of columns in the sys.objects catalog view, and it computes values for a new column named parent name.
  • The first two columns are for the child name and the child object_id columns. These columns are for primary_key_constraints.
  • The second two columns are for the parent objects of the child objects pointed at by the first two columns.

-- two query examples
-- 1st query is for sys.objects rows with type_desc of 'primary_key_constraint'
-- from the DataScience database
-- 2nd query is child and parent rows for sys.objects
use DataScience
go
 
-- list all objects in the dbo schema (schema_id = 1) of the DataScience database
-- of the current SQL Server instance 
-- with a type_desc value of 'primary_key_constraint'
select *
from sys.objects
where type_desc = 'primary_key_constraint'
 
-- list child object name, child object_id, parent name, and parent object_id
-- column values of the current SQL Server instance 
-- with a type_desc value of 'primary_key_constraint'
select name [child name], object_id [child object_id],
 object_name(parent_object_id) [parent name], parent_object_id
from sys.objects
where type_desc = 'primary_key_constraint'

Here is the result set for the first query:

• Each row in the result set has a type_desc value equal to 'primary_key_constraint.'
• The name and object_id columns are, respectively, for the primary key constraints.
• The parent_object_id column values are the parent object values.
• The other columns in the result set display other relevant values.
  • For example, the schema_id column values are all 1. These values indicate that the named object in the first column is from the dbo schema.
  • Also, the parent_object_id values are all less than the corresponding object_id values on the same row. This is because the parent objects were all created before their corresponding child objects.

Here is the result set for the second query. Like the preceding result set, the following screenshot has 10 rows – one for each sys.objects row with a primary key constraint. The parent name column values are computed via the built-in SQL Server function named object_name. This function returns an object name based on an object_id value (and optionally the database_id value for the underlying database).

• The first primary key constraint has name and object_id values, respectively, of PK__datedime__77387D0648A820BE and 399340487. The first parent_object_id value is 383340430.
• The last primary key constraint has name and object id values of, respectively, pk_symbol_date_decimal_tbl and 2026490298. The last parent_object_id is 2010490241.

Use Case and Row Examples for sys.tables and sys.columns

The sys.tables and sys.columns catalog views complement each other. Columns in a SQL Server table belong to tables. The object_id properties from the sys.columns catalog view and the sys.tables catalog view, respectively, indicate which SQL Server column objects belong to which SQL Server table object.

The sys.tables catalog view contains identifying information for table objects, such as the table name and object_id values. The sys.tables catalog view also contains a long list of table properties, such as whether the table has a primary key. Follow this hyperlink from the microsoft.learn.com site for property names and syntax examples for correctly referencing table-level object properties.

The sys.columns catalog view also has two sets of columns—one for identifying column set members and another for tracking column properties. There are three columns in the sys.columns catalog view for identifying set members. These columns are name, column_id, and object_id. T-SQL permits the matching of rows in the sys.columns catalog view to rows in the sys.tables catalog view by object_id value.

Among the most important sys.columns properties are those for:

• Revealing the data type for a column in a table,
• Indicating if a column can contain null values,
• The precision and scale for columns that store numeric values, and
• The maximum number of bytes for columns that store string values.

Here is a T-SQL script for listing the table name and table object_id metadata for the tables in a default database named DataScience. You can replace it with any other database to which you have metadata querying rights. The script has two segments. Its first segment ends with a pair of line comment markers. The @target_table_object_id local variable identifies the table for which matching column metadata is collected that starts after the concluding pair of line markers in the first segment.

The following T-SQL script has two segments. The first segment performs two steps:

1. List the sys.tables catalog view members by name and object_id.
2. Store the object_id value for one of the members in the @target_table_object_id local variable. The segment arbitrarily picks sys.tables catalog view member with the top object_id value. You can modify this code to pick any other sys.tables catalog view your application requires.

The second segment commences after the pair of line comment markers at the end of the first segment. This segment has two roles:

• First Role: List the table name and table object_id of the object_id assigned to the @target_table_object_id local variable in the first segment. The table name and table object_id are duplicated for each column in the SQL Server table selected by the first segment.
• Second Role: Display each column in the selected SQL Server table the distinct column identifiers and column names for each column in the selected SQL Server table from the first segment.

-- specify default database for script
use DataScience
go
 
-- declare local variable named @target_table_object_id
-- to specify a table from which to list column identifiers and names
declare @target_table_object_id int
 
-- list name and object_id for all user-tables in the default database
select 
 name [table name]
,object_id [table object_id]
from sys.tables;
 
-- selected table identifier from sys.tables
-- sample code is for the first object_id value in sys.tables
set @target_table_object_id =
(select top 1 object_id
from sys.tables)
 
-------------------------------------------------------------------------
-------------------------------------------------------------------------
 
-- list table name, table object_id, column identifier, and column name
select 
tables.name [table name]
,tables.object_id [table object_id]
,columns.column_id [column identifier for table object] 
,columns.name [column name]
from sys.columns columns
inner join sys.tables tables
on tables.object_id = columns.object_id
where tables.object_id = @target_table_object_id

The following screenshot shows the result set from the first and second code segments.

• The top portion of the screenshot is an excerpt for the first eight of 43 rows. These rows are from the first code segment.
• The bottom portion of the screenshot displays all seven rows for the result set from the second T-SQL segment.
  • The numeric values in the next to last column display the column_id values from the sys.columns catalog schema.
  • The name values in the last column show the column names from the sys.columns catalog schema.
• The total rows from the top and bottom screenshots is 50. The total rows in your adapted version of the script for your sample database will depend on the number of tables in the sample database and the number of columns in the selected table for list column identifiers and names.

Next Steps

This tip focuses on T-SQL metadata queries in SQL Server. These are T-SQL queries that access the metadata on a SQL Server instance. This tip introduces the basics of TSQL metadata queries. The design elements of SQL Server metadata follow from a set of metadata containers, often called catalog views.

• Databases can reside within a SQL Server instance.
• Within each SQL Server database, there are database objects.
• Databases can contain different types of objects, such as tables, columns, primary keys, foreign keys, and indexes.

As with many database programming topics, there are often multiple ways of gathering information about the same issue. When gathering SQL Server metadata, it is often convenient to search the contents of catalog views. It is sometimes the case that the same type of metadata can be queried from different catalog views. For example, you can query for primary keys from either the sys.objects catalog view or the sys.key_constraints catalog view.

This tip includes multiple examples of metadata queries for databases on a sample database instance. You can apply and tweak the code samples for the SQL Server instances to which you have metadata querying rights. Apply the T-SQL samples in this tip to the data sources for which you have metadata querying rights.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips