sys.objects (Transact-SQL)
Contains a row for each user-defined, schema-scoped object that is created within a database.
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.
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.
Note
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. |
See Also
Reference
Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
sys.all_objects (Transact-SQL)
sys.system_objects (Transact-SQL)
sys.triggers (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL)
TYPEPROPERTY (Transact-SQL)
Other Resources
User-Schema Separation
Implementing User-defined Types
Querying the SQL Server System Catalog FAQ