sys.objects (Transact-SQL)
TOC
Collapse the table of content
Expand the table of content

sys.objects (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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

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

System_CAPS_ICON_note.jpg Note


sys.objects does not show DDL triggers, because they are not schema-scoped. All triggers, both DML and DDL, are found in sys.triggers. sys.triggers supports a mixture of name-scoping rules for the various kinds of triggers.

Column nameData typeDescription
namesysnameObject name.
object_idintObject identification number. Is unique within a database.
principal_idintID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.

Is NULL if there is no alternate individual owner.

Is NULL if the object type is one of the following:

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

TA = Assembly (CLR-integration) trigger

TR = SQL trigger

UQ = UNIQUE constraint
schema_idintID of the schema that the object is contained in.

Schema-scoped system objects are always contained in the sys or INFORMATION_SCHEMA schemas.
parent_object_idintID of the object to which this object belongs.

0 = Not a child object.
typechar(2)Object type:

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SO = Sequence object

 

 Applies to: SQL Server 2012 through SQL Server 2016.

 

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

 

 Applies to: SQL Server 2016 through SQL Server 2016, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse.

 

ET = External Table
type_descnvarchar(60)Description of the object type:

AGGREGATE_FUNCTION

CHECK_CONSTRAINT

CLR_SCALAR_FUNCTION

CLR_STORED_PROCEDURE

CLR_TABLE_VALUED_FUNCTION

CLR_TRIGGER

DEFAULT_CONSTRAINT

EXTENDED_STORED_PROCEDURE

FOREIGN_KEY_CONSTRAINT

INTERNAL_TABLE

PLAN_GUIDE

PRIMARY_KEY_CONSTRAINT

REPLICATION_FILTER_PROCEDURE

RULE

SEQUENCE_OBJECT

 

 Applies to: SQL Server 2012 through SQL Server 2016.

 

SERVICE_QUEUE

SQL_INLINE_TABLE_VALUED_FUNCTION

SQL_SCALAR_FUNCTION

SQL_STORED_PROCEDURE

SQL_TABLE_VALUED_FUNCTION

SQL_TRIGGER

SYNONYM

SYSTEM_TABLE

TABLE_TYPE

UNIQUE_CONSTRAINT

USER_TABLE

VIEW
create_datedatetimeDate the object was created.
modify_datedatetimeDate the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered.
is_ms_shippedbitObject is created by an internal SQL Server component.
is_publishedbitObject is published.
is_schema_publishedbitOnly the schema of the object is published.

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

There is a version of this view with the same schema, called sys.system_objects, that shows system objects. There is another view called sys.all_objects that 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 = IT and type_desc = 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.

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

A. Returning all the objects that have been 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. Returning 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. Returning 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. Returning 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 JOIN sys.schemas AS s  
    ON s.schema_id = xsc.schema_id  
WHERE s.name = '<schema_name>';  
GO  
  

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)

Community Additions

ADD
Show:
© 2016 Microsoft