Interview Question of the Week #054 – Retrieve User Defined Object Details from sys.objects

Question: How do I retrieve details about user-defined objects? Query the current database’s sys.objects catalog view, then use the appropriate schema and parent-object functions for the IDs it contains.

An open-ended interview question gave a candidate room to show a favorite DBA script. The answer was simple: list database objects. It earned some attention because a useful inventory can answer several everyday questions without clicking through Object Explorer.

List foreign keys and their parent tables

-- Run in the database whose objects you want to inspect.
SELECT o.name AS ForeignKeyName,
       SCHEMA_NAME(o.schema_id) AS SchemaName,
       OBJECT_NAME(o.parent_object_id) AS ParentTableName,
       o.object_id,o.create_date,o.modify_date
FROM sys.objects AS o
WHERE o.type='F'
ORDER BY SchemaName,ParentTableName,ForeignKeyName;

-- Inventory types currently visible in this database.
SELECT type,type_desc,COUNT_BIG(*) AS ObjectCount
FROM sys.objects GROUP BY type,type_desc ORDER BY type;

The original query used OBJECT_NAME(schema_id). That is the wrong ID namespace. Use SCHEMA_NAME(schema_id) for the schema and OBJECT_NAME(parent_object_id) for the parent table. A schema name is also not the name of its owning principal; those are separate facts.

Change the type filter to answer another question

Common types are U for user tables, V for views, P for SQL procedures, FN for scalar SQL functions, IF for inline table-valued functions, and TF for multistatement table-valued functions.

Constraint types are F foreign key, PK primary key, UQ unique, C check and D default. Other original types include AF CLR aggregate, PC CLR procedure, FS CLR scalar function, FT CLR table-valued function, TA CLR DML trigger, TR SQL DML trigger, SN synonym, SQ service queue, R legacy rule, RF replication filter procedure, IT internal table, X extended procedure and S system base table.

Newer versions add types such as sequences and table types. The second query lists what is actually visible in your database instead of assuming an old list is exhaustive.

The catalog has a scope

sys.objects is a view, not a database. It describes schema-scoped objects in the current database. Use sys.system_objects for system objects and sys.all_objects for both scopes; database-level DDL triggers have their own catalog.

Metadata visibility follows permissions. An empty or partial result does not prove that no object exists. Use explicit columns rather than duplicate name and SELECT * output when building an inventory others will reuse.

Microsoft’s sys.objects reference lists the current types and catalog scope.

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