sys.objects (Transact-SQL)

Applies to: SQL Server 2022, SQL Server 2019, SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (APS)

Introduction

The sys.objects catalog view contains a row for each user-defined schema-scoped object in the database. Schema-scoped objects include tables, views, stored procedures, and more. This view provides metadata about these objects, such as their names, types, and creation dates.

Syntax

            
CREATE VIEW objects AS
SELECT
    [object_id],
    [name],
    [principal_id],
    [schema_id],
    [parent_object_id],
    [type],
    [type_desc],
    [content_type],
    [content_type_desc],
    [create_date],
    [modify_date],
    [is_ms_shipped],
    [is_published],
    [is_schema_bound],
    [default_object_id],
    [rule_object_id],
    [lock_escalation],
    [lock_escalation_desc]
FROM sys.sys.objects_vu
GO
            
        

Description

The sys.objects catalog view provides comprehensive information about schema-scoped objects within a SQL Server database. Each row represents a distinct object, and the columns provide details about its identity, type, temporal properties, and schema association.

Columns

Column Name	Data Type	Description
`object_id`	`int`	Unique identifier for the object.
`name`	`sysname`	Name of the object.
`schema_id`	`int`	Identifier of the schema that owns the object.
`parent_object_id`	`int`	Identifier of the parent object, if applicable (e.g., for constraints).
`type`	`char(2)`	Type of the object. For a list of possible values, see Object Types.
`type_desc`	`nvarchar(60)`	Description of the object type.
`create_date`	`datetime`	Date and time the object was created.
`modify_date`	`datetime`	Date and time the object was last modified.
`is_ms_shipped`	`bit`	Indicates if the object is shipped by Microsoft.
`is_schema_bound`	`bit`	Indicates if the object is schema-bound.

Permissions

Any user can query sys.objects to view their own objects. To view all objects in the database, a user typically needs the VIEW DEFINITION permission or membership in the db_owner fixed database role.

Examples

1. List all user-defined tables in the current database.

            
SELECT name, schema_id, create_date
FROM sys.objects
WHERE type = 'U' -- 'U' denotes a user-defined table
ORDER BY name;

2. Find objects created after a specific date.

            
SELECT name, type_desc, create_date
FROM sys.objects
WHERE create_date > '2023-01-01'
ORDER BY create_date DESC;

SQL Server Documentation