sys.objects (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined functions.

For more information, see Scalar User-Defined Functions for In-Memory OLTP.

Remarks

You can apply the OBJECT_ID, OBJECT_NAME, and OBJECTPROPERTY built-in functions to the objects shown in sys.objects.

There's a version of this view with the same schema, called sys.system_objects, that shows system objects. Another view, called sys.all_objects, shows both system and user objects. All three catalog views have the same structure.

In this version of SQL Server, an extended index, such as an XML index or spatial index, is considered an internal table in sys.objects (type is IT, and type_desc is INTERNAL_TABLE). For an extended index:

• name is the internal name of the index table
• parent_object_id is the object_id of the base table
• is_ms_shipped, is_published, and is_schema_published columns are set to 0

Related system views

Subsets of the objects can be viewed by using system views for a specific type of object, such as:

• sys.tables
• sys.views
• sys.procedures

Permissions

The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration.

Examples

A. Return all the objects that were modified in the last N days

Before you run the following query, replace <database_name> and <n_days> with valid values.

USE <database_name>;
GO

SELECT name AS object_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    type_desc,
    create_date,
    modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - <n_days>
ORDER BY modify_date;
GO

B. Return the parameters for a specified stored procedure or function

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>;
GO

SELECT SCHEMA_NAME(schema_id) AS schema_name,
    o.name AS object_name,
    o.type_desc,
    p.parameter_id,
    p.name AS parameter_name,
    TYPE_NAME(p.user_type_id) AS parameter_type,
    p.max_length,
    p.precision,
    p.scale,
    p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p
    ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')
ORDER BY schema_name,
    object_name,
    p.parameter_id;
GO

C. Return all the user-defined functions in a database

Before you run the following query, replace <database_name> with a valid database name.

USE <database_name>;
GO

SELECT name AS function_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    type_desc,
    create_date,
    modify_date
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO

D. Return the owner of each object in a schema

Before you run the following query, replace all occurrences of <database_name> and <schema_name> with valid names.

USE <database_name>;
GO

SELECT 'OBJECT' AS entity_type,
    USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name,
    name
FROM sys.objects
WHERE SCHEMA_NAME(schema_id) = '<schema_name>'

UNION

SELECT 'TYPE' AS entity_type,
    USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name,
    name
FROM sys.types
WHERE SCHEMA_NAME(schema_id) = '<schema_name>'

UNION

SELECT 'XML SCHEMA COLLECTION' AS entity_type,
    COALESCE(USER_NAME(xsc.principal_id), USER_NAME(s.principal_id)) AS owner_name,
    xsc.name
FROM sys.xml_schema_collections AS xsc
INNER JOIN sys.schemas AS s
    ON s.schema_id = xsc.schema_id
WHERE s.name = '<schema_name>';
GO

Related content

• System catalog views (Transact-SQL)
• sys.all_objects (Transact-SQL)
• sys.system_objects (Transact-SQL)
• sys.triggers (Transact-SQL)
• Object Catalog Views (Transact-SQL)
• Querying the SQL Server System Catalog FAQ
• sys.internal_tables (Transact-SQL)