sys.objects

Description

Analytics Platform System (PDW) SQL database in Microsoft Fabric Catalog views return information that is used by the SQL Server Database Engine. We recommend that you use catalog views because they are the most general interface to the catalog metadata, and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through Some catalog views inherit rows from other catalog views. For example, the view inherits from the catalog view. The catalog view is referred to as the base view, and the view is called the derived view. The returns the columns that are specific to tables and also all the columns that the filtered index, the number of rows might be less than the Azure SQL Database Specifies whether poison message handling is enabled for the queue. The default is ON.

Syntax

SELECT
*
FROM sys.objects
WHERE type
=
'IT'
;

Permissions

Article • 05/23/2023 Contains a row for each SQL Server stored procedure that was created as a numbered procedure. This does not show a row for the base (number = 1) stored procedure. Entries for the base stored procedures can be found in views such as and. Description ID of the object of the stored procedure. Number of this procedure within the object, 2 or greater. The SQL Server text that defines this procedure. NULL = encrypted. The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration. ） Important Numbered procedures are deprecated. Use of numbered procedures is discouraged. A DEPRECATION_ANNOUNCEMENT event is fired when a query that uses this catalog view is compiled. ﾉ Expand table ７ Note XML and CLR parameters are not supported for numbered procedures. See Also Warehouse in Microsoft Fabric SQL database in Microsoft Fabric Contains a row for each object that is a primary key or unique constraint. Includes PK and UQ. Description For a list of columns that this view inherits, see sys.objects (Transact-SQL). ID of the corresponding unique index in the parent object that was created to enforce this constraint. 1 = Name was generated by system. 0 = Name was supplied by the user. The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration. Object Catalog Views (Transact-SQL) Catalog Views (Transact-SQL) Querying the SQL Server System Catalog FAQ ﾉ Expand table See Also SQL database in Microsoft Fabric Contains a row for each object that is a procedure of some kind, with = P, X, RF, and PC. Description For a list of columns that this view inherits, see sys.objects (Transact-SQL) 1 = Procedure is auto-executed at the server startup; otherwise, 0. Can only be set for procedures in the master database. Execution of this procedure is replicated. Replication of the procedure execution is done only when the transaction can be serialized. During execution, the procedure skips constraints marked NOT FOR REPLICATION. The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration. Object Catalog Views (Transact-SQL) Catalog Views (Transact-SQL) ﾉ Expand table See Also sys.objects sys.key_constraints sys.filegroups sys.partition_schemes Querying the SQL Server System Catalog FAQ In-Memory OLTP overview and usage scenarios SQL database in Microsoft Fabric Returns a row for each object that is an event notification, with = EN. Description Event notification name. Object identification number. Is unique within a database. Class of parent. 0 = Database 1 = Object or Column DATABASE OBJECT_OR_COLUMN Non-zero ID of the parent object. 0 = The parent class is the database. Date created. Always equals. Name of the target service to which the notification is sent. Broker instance to which the notification is sent. ID of the database principal that owns this event notification. SID of the login who created the event notification. Is NULL if the FAN_IN option is not specified. The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration. ﾉ Expand table SQL database in Microsoft Fabric Contains a row for each synonym object that is = SN. Description For a list of columns that this view inherits, see sys.objects (Transact-SQL). Fully quoted name of the object to which the user of this synonym is redirected. The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration. Object Catalog Views (Transact-SQL) Catalog Views (Transact-SQL) ﾉ Expand table See Also SQL database in Microsoft Fabric Contains a row for each object that is a CHECK constraint, with = ‘C’. Description For a list of columns that this view inherits, see sys.objects (Transact-SQL). CHECK constraint is disabled. CHECK constraint was created with the NOT FOR REPLICATION option. CHECK constraint has not been verified by the system for all rows. 0 indicates a table-level CHECK constraint. Non-zero value indicates that this is a column-level CHECK constraint defined on the column with the specified ID value. SQL expression that defines this CHECK constraint. 1 = The constraint definition depends on the default collation of the database for correct evaluation; otherwise, 0. Such a dependency prevents changing the database default collation. 1 = Name was generated by system. 0 = Name was supplied by the user. The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration. ﾉ Expand table See Also SQL) Article • 05/23/2023 x) Captures the cryptographically protected history of database transactions against ledger tables in the database. A row in this view represents a database transaction. For more information on database ledger, see Ledger. Description A transaction ID that is unique for the database (it corresponds to a transaction ID in the database transaction log). A sequence number identifying a row. Offset of the transaction in the block. The time of the committing transaction. The name of the user who started the transaction. Captured by calling. This is a set of key-values pairs, stored in a binary format. The keys are object IDs (from ) of ledger database tables, modified by the transaction. Each value is a SHA-256 hash of all row versions a transaction created or invalidated. The binary format of data stored in this row is: , where - - indicates the encoding version. Length: 1 byte. - - the number of entries in the key-value pair list. Length: 1 byte. - - an object ID. Length: 4 bytes. - - the hash of rows the transaction cached in the table with the object ID stored as the key. Length: 32 bytes. Requires the permission. ﾉ Expand table The following query lists the permissions explicitly granted or denied to database principals. The following query joins sys.database_principals and sys.database_permissions to sys.objects and sys.schemas to list permissions granted or denied to specific schema objects. ） Important The permissions of fixed database roles do not appear in sys.database_permissions. Therefore, database principals may have additional permissions not listed here. Article • 02/28/2023 Description For a list of columns that this view inherits, see sys.objects (Transact-SQL). Maximum number of the concurrent readers allowed in the queue. Three-part name of the activation procedure. ID of the EXECUTE AS database principal. NULL by default or if EXECUTE AS CALLER. ID of the specified principal if EXECUTE AS SELF EXECUTE AS . -2 = EXECUTE AS OWNER. 1 = Activation is enabled. 1 = Receive is enabled. 1 = Enqueue is enabled. 1 = Messages are retained until dialog end. : SQL Server 2012 (11.x) and later. 1 = Poison message handling is enabled. The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration. ﾉ Expand table sys.objects (Transact-SQL) sys.spatial_index_tessellations (Transact-SQL) sys.indexes (Transact-SQL) sys.index_columns (Transact-SQL) Spatial Indexes Overview SQL database in Microsoft Fabric Many of the system tables from earlier releases of SQL Server are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000 (8.x). However, the compatibility views do not expose any of the metadata related to features that are introduced in SQL Server 2005 (9.x) and later. Therefore, when you use new features, such as Service Broker or partitioning, you must switch to using the catalog views. Another reason for upgrading to the catalog views is that compatibility view columns that store user IDs and type IDs may return NULL or trigger arithmetic overflows. This is because you can create more than 32,767 users, groups, and roles, and 32,767 data types. For example, if you were to create 32,768 users, and then run the following query:. If ARITHABORT is set to ON, the query fails with an arithmetic overflow error. If ARITHABORT is set to OFF, the column returns NULL. To avoid these problems, we recommend that you use the new catalog views that can handle the increased number of user IDs and type IDs. The following table lists the columns that are subject to this overflow. SQL Server 2005 view ﾉ Expand table DATABASE Indicates the scope of the DDL trigger applies to the current database. DATABASE must be specified if it was also specified when the trigger was created or modified. ALL SERVER : SQL Server 2008 (10.0.x) and later. Indicates the scope of the DDL trigger applies to the current server. ALL SERVER must be specified if it was also specified when the trigger was created or modified. ALL SERVER also applies to logon triggers. You can remove a DML trigger by dropping it or by dropping the trigger table. When a table is dropped, all associated triggers are also dropped. When a trigger is dropped, information about the trigger is removed from the , and catalog views. Multiple DDL triggers can be dropped per DROP TRIGGER statement only if all triggers were created using identical ON clauses. To rename a trigger, use DROP TRIGGER and CREATE TRIGGER. To change the definition of a trigger, use ALTER TRIGGER. For more information about determining dependencies for a specific trigger, see sys.sql_expression_dependencies , sys.dm_sql_referenced_entities (Transact-SQL) , and sys.dm_sql_referencing_entities (Transact-SQL). For more information about viewing the text of the trigger, see sp_helptext (Transact-SQL) and sys.sql_modules (Transact-SQL). For more information about viewing a list of existing triggers, see sys.triggers (Transact-SQL) and sys.server_triggers (Transact-SQL). ７ Note This option is not available in a contained database.

Remarks

Analytics Platform System (PDW)

SQL analytics endpoint in

Microsoft Fabric

Warehouse in Microsoft Fabric

Catalog views return information that is used by the SQL Server Database Engine. We

recommend that you use catalog views because they are the most general interface to the

catalog metadata, and provide the most efficient way to obtain, transform, and present

customized forms of this information. All user-available catalog metadata is exposed through

catalog views.

Some catalog views inherit rows from other catalog views. For example, the

view inherits from the

sys.objects

catalog view. The

catalog view is referred to as

the base view, and the

view is called the derived view. The

catalog view

returns the columns that are specific to tables and also all the columns that the

catalog view returns. The

catalog view returns rows for objects other than tables,

such as stored procedures and views. After a table is created, the metadata for the table is

returned in both views. Although the two catalog views return different levels of information

about the table, there is only one entry in metadata for this table with one name and one. This can be summarized as follows:

The base view contains a subset of columns and a superset of rows.

The derived view contains a superset of columns and a subset of rows.

The catalog views in SQL Server have been organized into the following categories:

Catalog views do not contain information about replication, backup, database

maintenance plan, or SQL Server Agent catalog data.

In future releases of SQL Server, Microsoft may augment the definition of any system

catalog view by adding columns to the end of the column list. We recommend against

using the syntax

in production code because the

number of columns returned might change and break your application.

filtered index, the number of rows might be less than the

number of rows in the table.

rows_sampled

Total number of rows sampled for statistics calculations.

Number of steps in the histogram. For more information, see

DBCC SHOW_STATISTICS (Transact-SQL)

unfiltered_rows

Total number of rows in the table before applying the filter

expression (for filtered statistics). If statistics are not filtered,

unfiltered_rows is equal to the value returns in the rows column.

modification_counter

Total number of modifications for the leading statistics column

(the column on which the histogram is built) since the last time

statistics were updated.

Memory-optimized tables: starting SQL Server 2016 (13.x) and in

this column contains: total number of

modifications for the table since the last time statistics were

updated or the database was restarted.

persisted_sample_percent

Persisted sample percentage used for statistic updates that do

not explicitly specify a sampling percentage. If value is zero, then

no persisted sample percentage is set for this statistic.

2016 (13.x) SP1 CU4

returns an empty rowset under any of the following conditions:

The specified object is not found or does not correspond to a table or indexed view.

The specified statistics ID does not correspond to existing statistics for the specified

The current user does not have permissions to view the statistics object.

This behavior allows for the safe usage of

when cross applied to

rows in views such as

Statistics update date is stored in the

statistics blob object

together with the

density vector

, not in the metadata. When no data is read to generate statistics data, the

statistics blob is not created, the date is not available, and the

last_updated

column is NULL.

This is the case for filtered statistics for which the predicate does not return any rows, or for

new empty tables.

Analytics Platform System (PDW)

SQL analytics endpoint in

Microsoft Fabric

Warehouse in Microsoft Fabric

Returns the schema ID associated with a schema name.

Database schemas act as namespaces or containers for objects, such as tables, views,

procedures, and functions, that can be found in the

catalog view.

Each schema has an owner. The owner is a security

The name of the schema.

schema_name

schema_name

isn’t specified,

returns the ID of the default schema of the caller.

is returned if

schema_name

isn’t a valid schema.

returns IDs of system schemas and user-defined schemas.

can be called

in a select list, in a

clause, and anywhere an expression is allowed.

Specifies whether poison message handling is enabled for the queue. The default is ON.

A queue that has poison message handling set to OFF will not be disabled after five

consecutive transaction rollbacks. This allows for a custom poison message handing system to

be defined by the application.

Specifies the SQL Server filegroup on which to create this queue. You can use the

parameter to identify a filegroup, or use the DEFAULT identifier to use the default filegroup for

the service broker database. In the context of this clause, DEFAULT is not a keyword, and must

be delimited as an identifier. When no filegroup is specified, the queue uses the default

filegroup for the database.

A queue can be the target of a SELECT statement. However, the contents of a queue can only

be modified using statements that operate on Service Broker conversations, such as SEND,

RECEIVE, and END CONVERSATION. A queue cannot be the target of an INSERT, UPDATE,

DELETE, or TRUNCATE statement.

A queue might not be a temporary object. Therefore, queue names starting with

Creating a queue in an inactive state lets you get the infrastructure in place for a service before

allowing messages to be received on the queue.

Service Broker does not stop activation stored procedures when there are no messages on the

queue. An activation stored procedure should exit when no messages are available on the

queue for a short time.

Permissions for the activation stored procedure are checked when Service Broker starts the

stored procedure, not when the queue is created. The CREATE QUEUE statement does not

verify that the user specified in the EXECUTE AS clause has permission to execute the stored

procedure specified in the PROCEDURE NAME clause.

When a queue is unavailable, Service Broker holds messages for services that use the queue in

the transmission queue for the database. The

catalog view provides a

view of the transmission queue.

A queue is a schema-owned object. Queues appear in the

catalog view.

The following table lists the columns in a queue.

Examples

Example 1

VacationHours

Example 2

HumanResources.Employee

Example 3

USE AdventureWorks2022;
GO
SELECT d.definition
FROM sys.default_constraints AS d
INNER JOIN sys.columns AS c
ON d.parent_object_id = c.object_id
AND d.parent_column_id = c.column_id

Example 4

sys.objects

Example 5

SELECT
*
FROM master.INFORMATION_SCHEMA.SCHEMATA;

Example 6

sys.objects

Example 7

sys.sql_modules

Example 8

CONTROL

Example 9

ALTER

Example 10

dbo.uspMyProc

(. and 2 more examples)