sys.objects, sys.all_objects and sys.system_objects

You searched for a procedure and got no row. sys.all_objects can explain the gap because the three object views cover different parts of the catalog.

Know What sys.objects, sys.system_objects, and sys.all_objects Hold

sys.objects lists schema scoped user defined objects in the current database. sys.system_objects lists schema scoped system objects. sys.all_objects combines those sets. Use the narrow view when the question is narrow. A report of application tables does not need a page of system procedures.

The names sound close enough to invite the wrong query. I see this when a DBA searches sys.objects for a system procedure and decides it does not exist. The procedure was simply in the other drawer. Try the wider view before turning the absence into a conclusion.

What object are you looking for, and who created it? That question tells you which view to ask first.

SELECT 'User' AS SourceView, COUNT(*) AS ObjectCount
FROM sys.objects
UNION ALL
SELECT 'System', COUNT(*)
FROM sys.system_objects
UNION ALL
SELECT 'All', COUNT(*)
FROM sys.all_objects;

Use sys.objects for Application Schema

For user tables, views, procedures, and constraints, sys.objects is a clean starting point. Include schema_id and type_desc. A name alone can repeat under different schemas, while the type distinguishes a table from a view or procedure.

I filter by type instead of scanning every row. A catalog list with one screen of useful objects is easier to read than a giant export. Keep object_id when you need to join to sys.columns, sys.indexes, or module definitions. It is the local key for those relationships.

Remember the current database. The same object name can exist elsewhere. Add DB_NAME to a diagnostic result when you plan to share it.

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

Use sys.system_objects for Built-In Names

System procedures and other schema scoped system objects appear in sys.system_objects. A familiar name such as sp_help belongs here. These objects are part of SQL Server’s system surface, not your application’s schema.

Do not assume every engine feature appears as a T-SQL procedure in this view. Some work is implemented in native code or exposed through different metadata. The view answers which schema scoped system objects are represented, not how the entire engine is built.

A system object definition can be visible through sys.system_sql_modules when it has SQL text and your account can see it. Reading that text can explain behavior, but supported documentation remains the contract for your application.

SELECT
    SCHEMA_NAME(schema_id) AS SchemaName,
    name, object_id, type_desc
FROM sys.system_objects
WHERE name LIKE N'sp_help%'
ORDER BY name;

Use sys.all_objects for One Search

When you do not yet know whether an object is user defined or built in, sys.all_objects is a practical search surface. It combines both groups. Once you find the row, decide whether a user view or system view is better for the rest of the investigation.

The combined view can add noise to an inventory report. Filtering on type alone does not always separate origin the way a reader expects. Use the source specific views when origin matters. The all view is best for discovery or code that truly needs both.

I run a name search first, then inspect schema and type. That small second step prevents a same name surprise.

SELECT
    SCHEMA_NAME(schema_id) AS SchemaName,
    name, object_id, type_desc
FROM sys.all_objects
WHERE name LIKE N'%help%'
ORDER BY SchemaName, name;

When the Query Returns Nothing

A catalog view only shows metadata your principal is allowed to see. An object can exist while a limited account gets no row. Check database context, spelling, schema, and permissions before declaring it absent. A different DBA seeing the row does not mean one of you queried a different server, though that can happen too.

Temporary objects need their own context. A local temporary table is represented in tempdb with an internal name and is scoped to the session. Searching the current user database for its name misses it. Choose the catalog that owns the object.

An empty result is evidence about this query under this account. It is not a universal statement about the server.

Remember the Schema Scope of sys.all_objects

These views cover schema scoped objects. Server level entities such as logins and linked servers have other catalog views. Databases have sys.databases. A query against sys.all_objects is broad within its scope, not an inventory of everything installed or configured.

The object_id is unique inside its database. Do not join an object_id from one database to sys.columns in another and expect a valid relationship. Carry database context with the key. The same integer can describe different objects in different databases.

I write this scope in the header of any reusable script. It avoids a report title like “Every SQL object” when the query sees only one database.

Compare Definitions When Needed

For user procedures and views, sys.sql_modules can show T-SQL definitions when visible. For system SQL modules, use sys.system_sql_modules. The all module view combines them. Object definitions are useful for understanding references and behavior, but a missing definition can reflect encryption, permissions, or implementation type.

If you need to find a table dependency, use sys.sql_expression_dependencies and a text search as complementary leads. Do not infer complete dynamic SQL coverage from a catalog relationship.

The three object views are a doorway into related catalogs. They answer what exists; the other views answer how each object is shaped and used.

Choose the Smallest Honest Query

Use sys.objects for an application schema report. Use sys.system_objects for a built-in procedure. Use sys.all_objects when origin is unknown. Keep the database, principal, and capture time beside the result when it matters to an incident.

If a query returns fewer objects than expected, investigate visibility before adding WITH hints or rewriting the report. Permissions are part of the result. A DBA query and an application login query can legitimately see different metadata.

The view names are easy to memorize once you think of the sets. User, system, and union. The hard part is remembering what your question actually asks.

Related reading on this blog: System Objects Not Visible in SQL Server Management Studio and Interview Question of the Week #054: Retrieve User Defined Object Details from sys.objects.

An empty sys.objects result is not proof of absence, it is a prompt to choose the right catalog and context.

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