The catalog views look separate until you follow their keys. Seeing how system views connect makes a table, its columns, and its indexes one readable map.
System Views Connect Through object_id Inside One Database
sys.objects gives each schema scoped object an object_id within the database. sys.columns and sys.indexes carry that same key for their parent object. Join on it when you want a table with its columns or indexes. Keep the database context beside the result. An object_id from another database can describe something else.
I begin with DB_NAME and a schema qualified object. That prevents a correct join from answering the wrong question. The same table name can appear in more than one schema. The numeric key settles which object the rows belong to after you choose the right database.
What is the parent object for the row you are reading? Ask that before joining another catalog. A view can expose many rows with familiar names, but the key tells you their actual relationship.
Follow schema_id to the Owner
sys.objects has schema_id. sys.schemas has one row per schema and its schema_id. Join them to display the schema name with the object. This is cleaner than guessing from a two part name stored in a comment.
An object name alone is not unique across schemas. Sales.Orders and Archive.Orders are different objects. A report that groups only by name can merge them and produce a convincing error. Carry schema_id or schema name through every result.
I include type_desc as well. A view and a table can have similar names and different change risks. The join below is a small starting map of tables and views.
SELECT
DB_NAME() AS DatabaseName,
s.name AS SchemaName,
o.name AS ObjectName,
o.object_id,
o.type_desc
FROM sys.objects AS o
JOIN sys.schemas AS s
ON s.schema_id = o.schema_id
WHERE o.type IN ('U', 'V')
ORDER BY s.name, o.name;Use column_id for Column Detail
sys.columns joins to sys.objects on object_id. column_id identifies a column inside that object. It is not a server wide column identifier. It can have gaps after schema changes, so do not treat it as a guaranteed row number from one through the count.
Join sys.types through user_type_id when you want the declared type name. A raw system_type_id join can multiply rows for alias types. Keep max_length, precision, scale, and nullability separate in a detailed report. The catalog is precise; your display formatting needs to be precise too.
The query below returns table columns with their parent. It is useful before a data type change or a deployment review.
SELECT
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS TableName,
c.column_id,
c.name AS ColumnName,
t.name AS TypeName
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;Pair object_id and index_id
sys.indexes has object_id for the table or view and index_id for an index within that object. The index_id value alone is not unique across the database. A join on index_id without object_id will mix unrelated indexes. I have seen that produce a lovely report that was completely wrong.
sys.index_columns carries both keys. It also has column_id, which points to a column of the same object. These three values form the useful path from table to index to participating column. Keep all of them in a detailed index report.
The index catalog describes definitions. It does not prove an application query uses an index. Use actual plans and workload evidence for that question.
Build the Full Index Column Join
Join sys.objects to sys.indexes on object_id. Join sys.index_columns on object_id and index_id. Join sys.columns on object_id and column_id. The repeated object_id checks keep every relationship inside the same parent. This is the part to copy carefully.
key_ordinal shows a key position. is_included_column distinguishes included columns. Some index types expose additional metadata, so read the relevant view documentation when the index is unusual. A general report is a starting point, not a substitute for understanding a specialized index.
I test the join on one known table before running it across a whole database. A small known case makes a bad join obvious.
SELECT
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS TableName,
i.name AS IndexName,
c.name AS ColumnName,
ic.key_ordinal,
ic.is_included_column
FROM sys.objects AS o
JOIN sys.indexes AS i
ON i.object_id = o.object_id
JOIN sys.index_columns AS ic
ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
JOIN sys.columns AS c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
WHERE o.type = 'U'
ORDER BY SchemaName, TableName, i.index_id, ic.index_column_id;Keep Scope and Visibility in View
Catalog metadata is scoped to the database and to the permissions of the caller. An empty join can reflect the wrong database, insufficient visibility, or a filter that excludes the object type. Check those before rewriting the key logic.
A temporary table lives in tempdb and has session behavior. A linked server object can refer outside the current database. Do not force those into the same local join without understanding where the metadata lives.
When I share a catalog result, I include database name and capture time. The keys are meaningful only with that context. A screenshot of object_id alone is a puzzle piece without the picture.
Avoid Invented Relationships When System Views Connect
Not every column named id joins to every other id. schema_id belongs with sys.schemas. object_id belongs to a parent object in the same database. index_id belongs with the object_id. column_id belongs with that same object. Let documented relationships guide the join.
If a result duplicates rows unexpectedly, inspect the join keys before adding DISTINCT. DISTINCT can hide the wrong relationship while keeping the wrong answer. Count rows for one known object and compare with a direct catalog query.
The best catalog scripts are small enough to audit. Add another view only after you know what each new key means.
Use the Map of How System Views Connect in Change Reviews
A schema change review can start with the object, list columns, then list indexes that include the affected column. Add constraints and dependencies from their own catalog views. This makes the effect of a proposed change visible before deployment.
Keep the SQL in a script library rather than exporting a static map once. New indexes and columns appear after each release. A repeatable query stays current with the database.
I come back to the four keys where system views connect whenever a catalog report looks suspicious. Most bad results are not mysterious. They are a missing part of a join.
Related reading on this blog: Finding the Right System View for the Question You Have and How to Build Three Part Name from Object_ID? Interview Question of the Week #134.
A catalog view is not an island, it is joined to the others by keys you can verify.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
