Exploring the System Catalog With Queries

You need to understand a database you did not build. Exploring the system catalog gives you a map from objects to columns, indexes, and dependencies.

Start Exploring the System Catalog With One Question

The catalog is a collection of related views. It is not a single table called “everything about my database.” Start with a question: which objects exist, what columns do they have, which indexes support them, or which modules reference them? Choose the view that answers it.

I start in the database that owns the object. DB_NAME confirms the context. Then I qualify the schema. An Orders table under sales is different from an Orders table under archive. A missing schema is a good way to inspect the wrong object with great confidence.

What are you trying to decide after the query? If the answer is unclear, narrow the question before adding more joins. A catalog query should help you act, not produce a screen full of metadata wallpaper.

List Objects With sys.objects

sys.objects lists schema scoped user objects visible to your account. It includes tables, views, procedures, constraints, and more. The type and type_desc columns tell you what each row represents. Filter deliberately. A table inventory that includes every constraint becomes noisy.

Join to sys.schemas or use SCHEMA_NAME to show the owning schema. Keep object_id in the output when you plan to join to other catalog views. The name is for people; object_id is the local join key. That identifier is unique within the database, not across all databases.

I save a small object list before changing an unfamiliar schema. It gives me a starting map and catches a view with the same name pattern as the table I expected.

SELECT
    DB_NAME() AS DatabaseName,
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS ObjectName,
    object_id,
    type_desc
FROM sys.objects
WHERE type IN ('U', 'V', 'P')
ORDER BY SchemaName, ObjectName;

Follow object_id to Columns

sys.columns uses object_id to point back to the owning object. Its column_id gives order within that object. Join to sys.types through user_type_id for the declared type. Add nullability, length, precision, and scale when you need a schema report.

Do not treat max_length as characters for every type. It is bytes. Do not treat a raw column list as a full CREATE TABLE script. Computed expressions, identity settings, defaults, and constraints live in other catalog views.

The join below shows a useful first pass for tables. If it returns no rows, check the current database, schema, and metadata permissions before announcing that no tables exist.

SELECT
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS TableName,
    c.column_id,
    c.name AS ColumnName,
    t.name AS TypeName,
    c.is_nullable
FROM sys.objects AS o
JOIN sys.columns AS c
  ON c.object_id = o.object_id
JOIN sys.types AS t
  ON t.user_type_id = c.user_type_id
WHERE o.type = 'U'
ORDER BY SchemaName, TableName, c.column_id;

Add Indexes Without Guessing

sys.indexes shares object_id with the table or view it belongs to. index_id distinguishes indexes within that object. sys.index_columns adds the column membership. Join on both object_id and index_id. A join on index_id alone mixes indexes from different tables. That is a report nobody wants to trust.

Look at type_desc, is_unique, is_disabled, and filter information before deciding what an index does. A filtered index is not a general replacement for an unfiltered one. Included columns differ from key columns. The catalog shows the definition, while actual workload evidence tells you whether it helps.

I compare index metadata with actual plans when performance is the question. An index existing in the catalog does not mean a particular query used it.

SELECT
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS TableName,
    i.name AS IndexName,
    i.index_id,
    i.type_desc,
    i.is_unique,
    i.is_disabled
FROM sys.objects AS o
JOIN sys.indexes AS i
  ON i.object_id = o.object_id
WHERE o.type = 'U'
  AND i.index_id > 0
ORDER BY SchemaName, TableName, i.index_id;

Trace Named Dependencies

sys.sql_expression_dependencies records by name references in persisted SQL expressions. Join referencing_id to sys.objects to identify the module that contains the reference. The view can help find a view or procedure affected by a table change.

It has limits. Dynamic SQL assembled at runtime is not fully visible to a static dependency view. Cross database references can report names without a resolved local object_id. A dependency report is a lead, not a complete guarantee that no other code touches the table.

Search module text and application code when a change is risky. Pair the catalog with a usage conversation. The database knows its stored definitions; it does not know every query hidden in a service.

SELECT
    SCHEMA_NAME(o.schema_id) AS ReferencingSchema,
    o.name AS ReferencingObject,
    d.referenced_schema_name,
    d.referenced_entity_name
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o
  ON o.object_id = d.referencing_id
ORDER BY ReferencingSchema, ReferencingObject;

Understand Empty Results When Exploring the System Catalog

Metadata visibility is permission based. An account can see objects it owns or can access, while another account sees more. Check permissions before interpreting an empty query. Also check whether the object is in another database or has been renamed.

System objects have their own catalog views. sys.all_objects includes user and system objects. sys.objects is usually the cleaner starting point for application schema work. Pick the wider view only when the question needs it.

A database context mistake is boring, but it is common. I put DB_NAME in exploratory output because it prevents a long discussion about a table that was never in the selected database.

Keep a Small Map for Exploring the System Catalog

Remember a few joins instead of memorizing every catalog view. object_id connects objects to columns and indexes. index_id connects an index to its column list. referencing_id and referenced_id describe resolved dependency ends. schema_id tells you where an object name belongs.

When you need a deeper answer, read the catalog view documentation and inspect a few rows. New versions add metadata columns. A saved query should select the columns it needs instead of assuming every release returns the same shape through SELECT star.

Exploring the system catalog turns a mystery database into a set of answerable questions. Start small, verify context, and follow the keys.

Use an object name and schema filter once you find the area of interest. A whole database map is useful for discovery, but a narrow query is better for a change ticket. Save the object_id and database name in the evidence. The same object_id value can appear in another database, so a bare number is incomplete. If you hand the query to another DBA, include the context that makes the result reproducible.

Related reading on this blog: Finding the Right System View for the Question You Have and Scripts to Retrieve Column Names.

The system catalog is not a maze, it is a map once you follow the join keys.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.