Export (0) Print
Expand All

sys.objects (Transact-SQL)

Contains a row for each user-defined, schema-scoped object that is created within a database.

ms190324.note(en-US,SQL.90).gifNote:
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.

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.

ms190324.note(en-US,SQL.90).gifNote:
All system objects are contained in the sys or INFORMATION_SCHEMA schemas.

Column name Data type Description

name

sysname

Object name.

object_id

int

Object identification number. Is unique within a database.

principal_id

int

ID 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_id

int

ID of the schema that the object is contained in.

For all schema-scoped system objects that ship with SQL Server 2005, this value will always be

IN (schema_id('sys'), schema_id('INFORMATION_SCHEMA')).

parent_object_id

int

ID of the object to which this object belongs.

0 = Not a child object.

type

char(2)

Object type:

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

PK = PRIMARY KEY constraint

P = SQL stored procedure

PC = Assembly (CLR) stored procedure

FN = SQL scalar function

FS = Assembly (CLR) scalar function

FT = Assembly (CLR) table-valued function

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

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TR = SQL DML trigger

IF = SQL inline table-valued function

TF = SQL table-valued-function

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

IT = Internal table

type_desc

nvarchar(60)

Description of the object type.

AGGREGATE_FUNCTION

CHECK_CONSTRAINT

DEFAULT_CONSTRAINT

FOREIGN_KEY_CONSTRAINT

PRIMARY_KEY_CONSTRAINT

SQL_STORED_PROCEDURE

CLR_STORED_PROCEDURE

SQL_SCALAR_FUNCTION

CLR_SCALAR_FUNCTION

CLR_TABLE_VALUED_FUNCTION

RULE

REPLICATION_FILTER_PROCEDURE

SYSTEM_TABLE

SYNONYM

SERVICE_QUEUE

CLR_TRIGGER

SQL_TRIGGER

SQL_INLINE_TABLE_VALUED_FUNCTION

SQL_TABLE_VALUED_FUNCTION

USER_TABLE

UNIQUE_CONSTRAINT

VIEW

EXTENDED_STORED_PROCEDURE

INTERNAL_TABLE

create_date

datetime

Date the object was created.

modify_date

datetime

Date 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_shipped

bit

Object is created by an internal SQL Server component.

is_published

bit

Object is published.

is_schema_published

bit

Only the schema of the object is published.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft