• Article

sys.all_objects (Transact-SQL)

Shows the UNION of all schema-scoped user-defined objects and system objects.

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, another owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.

Is NULL if there is no alternative 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) trigger

TR = SQL trigger

UQ = UNIQUE constraint

schema_id

int

ID of the schema that contains the object.

For all schema scoped system objects that are included with SQL Server, this value is always 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

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

TT = Table type

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

TABLE_TYPE

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 modified.

is_ms_shipped

bit

Object 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.

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.

Change History

Updated content

Added TT - Table type to the type column.