SQL Metadata in sys.databases, sys.objects, sys.tables and sys.columns (2024)

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 residein nested containers. The outermost container is a SQL Server instance.Within a SQL Server instance, data can be organized in databases. Some databasesare 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, aimsto help the reader understand the structure and use cases for sys schema catalogviews. The T-SQL examples in this tip will give you exposure to system catalogviews, such as:

• sys.databasesis a built-in SQL Server catalog view that itemizes the databases on a SQL Serverinstance. Columns within each sys.database's row designate the nameand other properties of each database on a SQL Server instance.
• sys.objectsis a built-in SQL Server catalog view that itemizes user-defined and system-definedobjects and their relationships within each database on a SQL Server instance.
• sys.tables isa built-in SQL Server catalog view that itemizes the user-defined tables residingwithin a SQL Server database engine. The view contains a row for eachtable created by a user within a database.
• sys.columnsis a built-in catalog view that can track the columns within each table withineach database of a SQL Server instance. There is a row in sys.columnsfor columns from both system-created and user-created table objects within adatabase.

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

• "StarterMetadata T-SQL Query Examples for Databases and Tables"
• "SQLServer Code Samples for Sys.objects and Related Functions"
• "Over40 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 Serversys schema catalog view. The information_schema catalog view is explicitlydesigned to query metadata from any database subscribing to the SQL-92 standard.In contrast, sys schema catalog views usually operate faster on SQL Server instancesand 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 onthe databases within a SQL Server instance. The following script returns alist of the databases on a SQL Server instance. Each row in the result setreveals 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. Databasenames have a sysname datatype, which is equivalent to a nvarchar (128) datatypevalue.
• 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 instanceselect name [database name],database_id,create_datefrom 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 databasesare server-generated and are derived from SQL Server installation files. Themodel database is initially server-generated but can be updated after installation.Additional databases can be created depending on your database configuration choicesduring and after installation. MSSQLTips.com offersan 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 reflectthe 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.5through 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 metadatafor:

• 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 reviewthe types of metadata that are available for the SQL Server objects. Thesemetadata items are denoted by the columns of the sys.objects catalog view.

The following script selects three columns from the sys.objects catalog viewwithin the currently active schema in the currently active database. The namecolumn indicates the name value from within each row of sys.objects. The object_idcolumn values in sys.objects contain unique integer identifiers for each objectwithin the currently active schema. The type_desc column reflects the typeof value for an object in sys.objects. Typical kinds of type_desc values includeuser_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 Instanceselect name, object_id, type_desc from sys.objectsorder by type_desc

The following screenshot displays the first 23 rows for the result set from thepreceding 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 catalogview:

• The use statement towards the top of the script designates DataScience asthe active database from which to extract metadata rows. You can designateany 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 tothose with a type_desc value of 'primary_key_constraint.'
• The second query takes a subset of the columns from the full set of columnsin the sys.objects catalog view, and it computes values for a new column namedparent name.
  • The first two columns are for the child name and the child object_idcolumns. These columns are for primary_key_constraints.
  • The second two columns are for the parent objects of the child objectspointed 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.objectsuse DataSciencego -- 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.objectswhere 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_idfrom sys.objectswhere 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 valuesindicate that the named object in the first column is from the dbo schema.
  • Also, the parent_object_id values are all less than the correspondingobject_id values on the same row. This is because the parent objectswere all created before their corresponding child objects.

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

• The first primary key constraint has name and object_id values, respectively,of PK__datedime__77387D0648A820BE and 399340487. The first parent_object_idvalue 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 is2010490241.

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

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

The sys.tables catalog view contains identifyinginformation 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, suchas whether the table has a primary key. Followthis hyperlink from the microsoft.learn.com sitefor property names and syntax examples for correctly referencing table-level objectproperties.

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

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 metadatafor the tables in a default database named DataScience. You can replace itwith any other database to which you have metadata querying rights. The scripthas 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 matchingcolumn metadata is collected that starts after the concluding pair of line markersin the first segment.

The following T-SQL script has two segments. The first segment performstwo 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_idlocal variable. The segment arbitrarily picks sys.tables catalog viewmember with the top object_id value. You can modify this code to pickany other sys.tables catalog view your application requires.

The second segment commences after the pair of line comment markers at the endof 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_idlocal variable in the first segment. The table name and table object_idare duplicated for each column in the SQL Server table selected by the firstsegment.
• Second Role:Display each column in the selected SQL Server table the distinct column identifiersand column names for each column in the selected SQL Server table from the firstsegment.

-- specify default database for scriptuse DataSciencego -- declare local variable named @target_table_object_id-- to specify a table from which to list column identifiers and namesdeclare @target_table_object_id int -- list name and object_id for all user-tables in the default databaseselect 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.tablesset @target_table_object_id =(select top 1 object_idfrom sys.tables) -------------------------------------------------------------------------------------------------------------------------------------------------- -- list table name, table object_id, column identifier, and column nameselect 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 columnsinner join sys.tables tableson tables.object_id = columns.object_idwhere tables.object_id = @target_table_object_id

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

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

Next Steps

This tip focuses on T-SQL metadata queries in SQL Server. These are T-SQLqueries that access the metadata on a SQL Server instance. This tip introducesthe basics of TSQL metadata queries. The design elements of SQL Server metadatafollow 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 gatheringinformation about the same issue. When gathering SQL Server metadata, it isoften convenient to search the contents of catalog views. It is sometimesthe 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 catalogview or the sys.key_constraints catalog view.

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

About the author

Article Last Updated: 2024-08-28