sys.objects (Transact-SQL)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

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.

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. Schema-scoped system objects are always contained in the sys or INFORMATION_SCHEMA schemas.
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 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 SQ = Service queue TA = Assembly (CLR) DML trigger TF = SQL table-valued-function TR = SQL DML trigger U = Table (user-defined) UQ = UNIQUE constraint V = View X = Extended stored procedure
type_desc	nvarchar(60)	Description of the object type: AGGREGATE_FUNCTION CHECK_CONSTRAINT DEFAULT_CONSTRAINT FOREIGN_KEY_CONSTRAINT SQL_SCALAR_FUNCTION CLR_SCALAR_FUNCTION CLR_TABLE_VALUED_FUNCTION SQL_INLINE_TABLE_VALUED_FUNCTION INTERNAL_TABLE SQL_STORED_PROCEDURE CLR_STORED_PROCEDURE PLAN_GUIDE PRIMARY_KEY_CONSTRAINT RULE REPLICATION_FILTER_PROCEDURE SYSTEM_TABLE SYNONYM SERVICE_QUEUE CLR_TRIGGER SQL_TABLE_VALUED_FUNCTION SQL_TRIGGER USER_TABLE UNIQUE_CONSTRAINT VIEW EXTENDED_STORED_PROCEDURE
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.

Remarks

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.

Permissions

In SQL Server 2005 and later versions, 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.

Examples

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, o.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

A. Returning all the objects that have been modified in the last N days

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

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

D. Returning the owner of each object in a schema.

Reference

Other Resources

Help and Information