Skip to content

Latest commit

 

History

History
183 lines (145 loc) · 10.2 KB

sys-objects-transact-sql.md

File metadata and controls

183 lines (145 loc) · 10.2 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sys.objects (Transact-SQL)
Contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined functions.
rwestMSFT
randolphwest
04/12/2024
sql
system-objects
reference
sys.objects_TSQL
objects
sys.objects
objects_TSQL
sys.objects catalog view
table-valued parameters, sys.objects catalog view
user-defined table types [SQL Server]
table types [SQL Server]
TSQL
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

sys.objects (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]

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.

Note

sys.objects doesn't show DDL triggers, because they aren't 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.

NULL if there's no alternate individual owner.

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

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
EC = Edge 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 (TVF)
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
U = Table (user-defined)
V = View

Applies to: [!INCLUDE ssSQL11] and later versions

SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function (TVF)
TR = SQL DML trigger
TT = Table type
UQ = unique constraint
X = Extended stored procedure

Applies to: [!INCLUDE sssql14] and later versions, [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuresynapse-md], [!INCLUDE ssPDW]

ST = Statistics tree

Applies to: [!INCLUDE sssql16-md] and later versions, [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuresynapse-md], [!INCLUDE ssPDW]

ET = External table

Applies to: [!INCLUDE sssql16-md] and later versions, [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuresynapse-md], [!INCLUDE ssPDW]

EC = Edge constraint
type_desc nvarchar(60) Description of the object type:

AGGREGATE_FUNCTION
CHECK_CONSTRAINT
CLR_SCALAR_FUNCTION
CLR_STORED_PROCEDURE
CLR_TABLE_VALUED_FUNCTION
CLR_TRIGGER
DEFAULT_CONSTRAINT
EDGE_CONSTRAINT
EXTENDED_STORED_PROCEDURE
FOREIGN_KEY_CONSTRAINT
INTERNAL_TABLE
PLAN_GUIDE
PRIMARY_KEY_CONSTRAINT
REPLICATION_FILTER_PROCEDURE
RULE
SEQUENCE_OBJECT
SERVICE_QUEUE
SQL_INLINE_TABLE_VALUED_FUNCTION
SQL_SCALAR_FUNCTION
SQL_STORED_PROCEDURE
SQL_TABLE_VALUED_FUNCTION
SQL_TRIGGER
SYNONYM
SYSTEM_TABLE
TYPE_TABLE
UNIQUE_CONSTRAINT
USER_TABLE
VIEW
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 an index on the table or view is created or altered.
is_ms_shipped bit Object is created by an internal [!INCLUDE ssNoVersion] 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'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 [!INCLUDE ssNoVersion], 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:

Permissions

[!INCLUDE ssCatViewPerm] 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