sys.objects (Transact-SQL)

Returns a row for each user-defined or system object that is created within a database.

Applies to: SQL Server 2008 and later | Azure SQL Database | Azure Synapse Analytics | Parallel Data Warehouse

This catalog view contains a row for each schema-scoped object in the database.

Syntax

SELECT column_name, ...
FROM sys.objects [ AS object_alias ]
[ WHERE  ]
[ ORDER BY  ];

Permissions

Requires membership in the public role. If the caller has been granted the CONTROL permission on the database, the caller can see all objects in the database.

Description

The sys.objects catalog view contains a row for each user-defined or system object that is created within a database. These objects include tables, views, stored procedures, functions, triggers, and more.

Columns

Column Name Data Type Description
object_id int Unique identifier for the object.
name nvarchar(128) Name of the object.
schema_id int ID of the schema that owns the object.
parent_object_id int ID of the parent object if the object is a child object.
type char(2) Type of object. See common object types below.
type_desc nvarchar(60) Description of the object type.
create_date datetime Date the object was created.
modify_date datetime Date the object was last modified.
is_ms_shipped bit Indicates if the object is shipped by Microsoft.
is_published bit Indicates if the object is published for replication.
is_schema_bound bit Indicates if the object is schema-bound.

Common Object Types (type column)

  • U = User-defined table
  • V = View
  • P = Stored Procedure
  • FN = SQL scalar function
  • IF = SQL inline table-valued function
  • TF = SQL table-valued function
  • TR = Trigger
  • S = System table
  • C = CHECK constraint
  • D = DEFAULT constraint
  • PK = PRIMARY KEY constraint
  • FK = FOREIGN KEY constraint
  • UQ = UNIQUE constraint

Examples

List all tables in the database

SELECT
    name AS TableName,
    SCHEMA_NAME(schema_id) AS SchemaName,
    create_date,
    modify_date
FROM
    sys.objects
WHERE
    type = 'U'
ORDER BY
    name;

Find stored procedures created after a specific date

SELECT
    name AS ProcedureName,
    SCHEMA_NAME(schema_id) AS SchemaName,
    create_date
FROM
    sys.objects
WHERE
    type = 'P' AND create_date > '2023-01-01'
ORDER BY
    create_date DESC;

See Also