- sys.all_columns
- sys.all_objects
- sys.all_parameters
- sys.all_sql_modules
- sys.all_views
- sys.allocation_units
- sys.assembly_modules
- sys.check_constraints
- sys.columns
- sys.computed_columns
- sys.default_constraints
- sys.event_notifications
- sys.events
- sys.extended_procedures
- sys.foreign_key_columns
- sys.foreign_keys
- sys.function_order_columns
- sys.functions
- sys.identity_columns
- sys.index_columns
- sys.indexes
- sys.internal_tables
- sys.key_constraints
- sys.numbered_procedure_parameters
- sys.numbered_procedures
- sys.objects
- sys.parameters
- sys.partitions
- sys.procedures
- sys.rules
- sys.sql_dependencies
- sys.sql_expression_dependencies
- sys.sql_modules
- sys.stats
- sys.stats_columns
- sys.synonyms
- sys.system_columns
- sys.system_functions
- sys.system_objects
- sys.system_parameters
- sys.system_stored_procedures
- sys.system_views
- sys.table_types
- sys.tables
- sys.trigger_event_types
- sys.trigger_events
- sys.triggers
- sys.views
sys.all_columns
Shows the union of all columns belonging to user-defined objects and system objects.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object to which this column belongs. |
name | • | • | • | • | sysname | Name of the column. Is unique within the object. |
column_id | • | • | • | • | int | ID of the column. Is unique within the object. Column IDs might not be sequential. |
system_type_id | • | • | • | • | tinyint | ID of the system-type of the column. |
user_type_id | • | • | • | • | int | ID of the type of the column as defined by the user. To return the name of the type, join to the sys.types1 catalog view on this column. |
max_length | • | • | • | • | smallint | Maximum length (in bytes) of the column. 1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml. For text columns, the max_length value will be 16 or the value set by sp_tableoption 'text in row'. |
precision | • | • | • | • | tinyint | Precision of the column if numeric-based; otherwise, 0. |
scale | • | • | • | • | tinyint | Scale of the column if numeric-based; otherwise, 0. |
collation_name | • | • | • | • | sysname | Name of the collation of the column if character-based; otherwise, NULL. |
is_nullable | • | • | • | • | bit | 1 = Column is nullable. |
is_ansi_padded | • | • | • | • | bit | 1 = Column uses ANSI_PADDING ON behavior if character, binary, or variant. 0 = Column is not character, binary, or variant. |
is_rowguidcol | • | • | • | • | bit | 1 = Column is a declared ROWGUIDCOL. |
is_identity | • | • | • | • | bit | 1 = Column has identity values |
is_computed | • | • | • | • | bit | 1 = Column is a computed column. |
is_filestream | • | • | • | • | bit | 1 = Column is declared to use filestream storage. |
is_replicated | • | • | • | • | bit | 1 = Column is replicated. |
is_non_sql_subscribed | • | • | • | • | bit | 1 = Column has a non-SQL Server subscriber. |
is_merge_published | • | • | • | • | bit | 1 = Column is merge-published. |
is_dts_replicated | • | • | • | • | bit | 1 = Column is replicated by using SQL Server 2005 Integration Services (SSIS). |
is_xml_document | • | • | • | • | bit | 1 = Content is a complete XML document. 0 = Content is a document fragment, or the column data type is not XML. |
xml_collection_id | • | • | • | • | int | Non-zero if the column's data type is xml and the XML is typed. The value will be the ID of the collection containing the column's validating XML schema namespace 0 = no XML schema collection. |
default_object_id | • | • | • | • | int | ID of the default object, regardless of whether it is a stand-alone sys.sp_bindefault2, or an in-line, column-level DEFAULT constraint. The parent_object_id column of an inline column-level default object is a reference back to the table itself. 0 = No default. |
rule_object_id | • | • | • | • | int | ID of the stand-alone rule bound to the column by using sys.sp_bindrule. 0 = No stand-alone rule. For column-level CHECK constraints, see sys.check_constraints (Transact-SQL)3. |
is_sparse | • | • | • | bit | 1 = Column is a sparse column. For more information, see Using Sparse Columns4. | |
is_column_set | • | • | • | bit | 1 = Column is a column set. For more information, see Using Column Sets5. |
TSQL
Sql 2005SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id] FROM sys.all_columns
Sql 2008
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set] FROM sys.all_columns
Sql 2008 R2
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set] FROM sys.all_columns
Sql 2012
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set] FROM sys.all_columns
Back to Top
sys.all_objects
Shows the UNION of all schema-scoped user-defined objects and system objects.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | 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 2005, 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 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) trigger TR = SQL trigger IF = SQL inlined 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 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. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.all_objects
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.all_objects
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.all_objects
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.all_objects
Back to Top
sys.all_parameters
Shows the union of all parameters that belong to user-defined or system objects.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object to which this parameter belongs. |
name | • | • | • | • | sysname | Name of parameter. Is unique within the object. If the object is a scalar function, the parameter name is an empty string in the row representing the return value. |
parameter_id | • | • | • | • | int | ID of parameter. Is unique within the object. If the object is a scalar function, parameter_id = 0 represents the return value. |
system_type_id | • | • | • | • | tinyint | ID of the system type of the parameter. |
user_type_id | • | • | • | • | int | ID of the type of the parameter as defined by the user. To return the name of the type, join to the sys.types1 catalog view on this column. |
max_length | • | • | • | • | smallint | Maximum length of the parameter, in bytes. -1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml. |
precision | • | • | • | • | tinyint | Precision of the parameter if it is numeric-based; otherwise, 0. |
scale | • | • | • | • | tinyint | Scale of the parameter if it is numeric-based; otherwise, 0. |
is_output | • | • | • | • | bit | 1 = Parameter is output (or return); otherwise, 0. |
is_cursor_ref | • | • | • | • | bit | 1 = Parameter is a cursor reference parameter. |
has_default_value | • | • | • | • | bit | 1 = Parameter has a default value. SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column will always have a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules2 catalog view, or use the OBJECT_DEFINITION3 system function. |
is_xml_document | • | • | • | • | bit | 1 = Content is a complete XML document. 0 = Content is a document fragment or the data type of the column is not xml. |
default_value | • | • | • | • | sql_variant | If has_default_value is 1, the value of this column is the value of the default for the parameter; otherwise, NULL. |
xml_collection_id | • | • | • | • | int | Is the ID of the XML schema collection used to validate the parameter. Nonzero if the data type of the parameter is xml and the XML is typed. 0 = There is no XML schema collection, or the parameter is not XML. |
TSQL
Sql 2005SELECT [object_id], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref], [has_default_value], [is_xml_document], [default_value], [xml_collection_id] FROM sys.all_parameters
Sql 2008
SELECT [object_id], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref], [has_default_value], [is_xml_document], [default_value], [xml_collection_id] FROM sys.all_parameters
Sql 2008 R2
SELECT [object_id], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref], [has_default_value], [is_xml_document], [default_value], [xml_collection_id] FROM sys.all_parameters
Sql 2012
SELECT [object_id], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref], [has_default_value], [is_xml_document], [default_value], [xml_collection_id] FROM sys.all_parameters
Back to Top
sys.all_sql_modules
Returns the union of sys.sql_modules and sys.system_sql_modules.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object of the containing object. Is unique within a database. |
definition | • | • | • | • | nvarchar(max) | SQL text that defines this module. NULL = Encrypted |
uses_ansi_nulls | • | • | • | • | bit | Module was created with SET ANSI_NULLS ON. |
uses_quoted_identifier | • | • | • | • | bit | Module was created with SET QUOTED_IDENTIFIER ON. |
is_schema_bound | • | • | • | • | bit | Module was created with the SCHEMABINDING option. |
uses_database_collation | • | • | • | • | bit | 1 = Schema-bound module definition depends on the default-collation of the database for correct evaluation; otherwise, 0. Such a dependency prevents changing the default collation of the database. |
is_recompiled | • | • | • | • | bit | Procedure was created using the WITH RECOMPILE option. |
null_on_null_input | • | • | • | • | bit | Module was declared to produce a NULL output on any NULL input. |
execute_as_principal_id | • | • | • | • | int | ID of the EXECUTE AS database principal. NULL by default or if EXECUTE AS CALLER. ID of the specified principal if EXECUTE AS SELF or EXECUTE AS -2 = EXECUTE AS OWNER. |
is_contained | • | bit | Indicates if a module in a contained database is contained. 0 = The module is not contained. 1 = The module is contained. Does not apply to SQL Azure. |
TSQL
Sql 2005SELECT [object_id], [definition], [uses_ansi_nulls], [uses_quoted_identifier], [is_schema_bound], [uses_database_collation], [is_recompiled], [null_on_null_input], [execute_as_principal_id] FROM sys.all_sql_modules
Sql 2008
SELECT [object_id], [definition], [uses_ansi_nulls], [uses_quoted_identifier], [is_schema_bound], [uses_database_collation], [is_recompiled], [null_on_null_input], [execute_as_principal_id] FROM sys.all_sql_modules
Sql 2008 R2
SELECT [object_id], [definition], [uses_ansi_nulls], [uses_quoted_identifier], [is_schema_bound], [uses_database_collation], [is_recompiled], [null_on_null_input], [execute_as_principal_id] FROM sys.all_sql_modules
Sql 2012
SELECT [object_id], [definition], [uses_ansi_nulls], [uses_quoted_identifier], [is_schema_bound], [uses_database_collation], [is_recompiled], [null_on_null_input], [execute_as_principal_id], [is_contained] FROM sys.all_sql_modules
Back to Top
sys.all_views
Shows the UNION of all user-defined and system views.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | • | sysname | sys.objects | Object name. |
object_id | • | • | • | • | int | sys.objects | Object identification number. Is unique within a database. |
principal_id | • | • | • | • | int | sys.objects | 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 | sys.objects | 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 | sys.objects | ID of the object to which this object belongs. 0 = Not a child object. |
type | • | • | • | • | char(2) | sys.objects | 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) | sys.objects | 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 | sys.objects | Date the object was created. |
modify_date | • | • | • | • | datetime | sys.objects | 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 | sys.objects | Object is created by an internal SQL Server component. |
is_published | • | • | • | • | bit | sys.objects | Object is published. |
is_schema_published | • | • | • | • | bit | sys.objects | Only the schema of the object is published. |
is_replicated | • | • | • | • | bit | 1 = View is replicated. | |
has_replication_filter | • | • | • | • | bit | 1 = View has a replication filter. | |
has_opaque_metadata | • | • | • | • | bit | 1 = VIEW_METADATA option specified for view. For more information, see CREATE VIEW (Transact-SQL)2. | |
has_unchecked_assembly_data | • | • | • | • | bit | 1 = Table contains persisted data that depends on an assembly whose definition changed during the last ALTER ASSEMBLY. Resets to 0 after the next successful DBCC CHECKDB or DBCC CHECKTABLE. | |
with_check_option | • | • | • | • | bit | 1 = WITH CHECK OPTION was specified in the view definition. | |
is_date_correlation_view | • | • | • | • | bit | 1 = View was created automatically by the system to store correlation information between datetime columns. Creation of this view was enabled by setting DATE_CORRELATION_OPTIMIZATION to ON. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_replicated], [has_replication_filter], [has_opaque_metadata], [has_unchecked_assembly_data], [with_check_option], [is_date_correlation_view] FROM sys.all_views
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_replicated], [has_replication_filter], [has_opaque_metadata], [has_unchecked_assembly_data], [with_check_option], [is_date_correlation_view] FROM sys.all_views
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_replicated], [has_replication_filter], [has_opaque_metadata], [has_unchecked_assembly_data], [with_check_option], [is_date_correlation_view] FROM sys.all_views
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_replicated], [has_replication_filter], [has_opaque_metadata], [has_unchecked_assembly_data], [with_check_option], [is_date_correlation_view] FROM sys.all_views
Back to Top
sys.allocation_units
allocation unit in the database.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
allocation_unit_id | • | • | • | • | bigint | ID of the allocation unit. Is unique within a database. |
type | • | • | • | • | tinyint | Type of allocation unit. 0 = Dropped 1 = In-row data (all data types, except LOB data types) 2 = Large object (LOB) data (text, ntext, image, xml, large value types, and CLR user-defined types) 3 = Row-overflow data |
type_desc | • | • | • | • | nvarchar(60) | Description of the allocation unit type. DROPPED IN_ROW_DATA LOB_DATA ROW_OVERFLOW_DATA |
container_id | • | • | • | • | bigint | ID of the storage container associated with the allocation unit. If type = 1 or 3, then container_id = sys.partitions.hobt_id. If type is 2, then container_id = sys.partitions.partition_id. 0 = Allocation unit marked for deferred drop |
data_space_id | • | • | • | • | int | ID of the filegroup in which this allocation unit resides. |
total_pages | • | • | • | • | bigint | Total number of pages allocated or reserved by this allocation unit. |
used_pages | • | • | • | • | bigint | Number of total pages actually in use. |
data_pages | • | • | • | • | bigint | Number of used pages that have: In-row data LOB data Row-overflow data Value returned excludes internal index pages and allocation-management pages. |
TSQL
Sql 2005SELECT [allocation_unit_id], [type], [type_desc], [container_id], [data_space_id], [total_pages], [used_pages], [data_pages] FROM sys.allocation_units
Sql 2008
SELECT [allocation_unit_id], [type], [type_desc], [container_id], [data_space_id], [total_pages], [used_pages], [data_pages] FROM sys.allocation_units
Sql 2008 R2
SELECT [allocation_unit_id], [type], [type_desc], [container_id], [data_space_id], [total_pages], [used_pages], [data_pages] FROM sys.allocation_units
Sql 2012
SELECT [allocation_unit_id], [type], [type_desc], [container_id], [data_space_id], [total_pages], [used_pages], [data_pages] FROM sys.allocation_units
Back to Top
sys.assembly_modules
function, procedure or trigger that is defined by a common language runtime (CLR) assembly. This catalog view maps CLR stored procedures, CLR triggers, or CLR functions to their underlying implementation. Objects of type TA, AF, PC, FS, and FT have an associated assembly module. To find the association between the object and the assembly, you can join this catalog view to other catalog views. For example, when you create a CLR stored procedure, it is represented by one row in sys.objects, one row in sys.procedures (which inherits from sys.objects), and one row in sys.assembly_modules. The stored procedure itself is represented by the metadata in sys.objects and sys.procedures. References to the procedure€™s underlying CLR implementation are found in sys.assembly_modules.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | Object identification number of the SQL object. Is unique within a database. |
assembly_id | • | • | • | • | int | ID of the assembly from which this module was created. |
assembly_class | • | • | • | • | sysname | Name of the class within the assembly that defines this module. |
assembly_method | • | • | • | • | sysname | Name of the method within the assembly_class that defines this module. NULL for aggregate functions (AF). |
null_on_null_input | • | • | • | • | bit | Module was declared to produce a NULL output for any NULL input. |
execute_as_principal_id | • | • | • | • | int | ID of the database principal under which the context execution occurs, as specified by the EXECUTE AS clause of the CLR function, stored procedure, or trigger. NULL = EXECUTE AS CALLER. This is the default. ID of the specified database principal = EXECUTE AS SELF, EXECUTE AS user_name, or EXECUTE AS login_name. -2 = EXECUTE AS OWNER. |
TSQL
Sql 2005SELECT [object_id], [assembly_id], [assembly_class], [assembly_method], [null_on_null_input], [execute_as_principal_id] FROM sys.assembly_modules
Sql 2008
SELECT [object_id], [assembly_id], [assembly_class], [assembly_method], [null_on_null_input], [execute_as_principal_id] FROM sys.assembly_modules
Sql 2008 R2
SELECT [object_id], [assembly_id], [assembly_class], [assembly_method], [null_on_null_input], [execute_as_principal_id] FROM sys.assembly_modules
Sql 2012
SELECT [object_id], [assembly_id], [assembly_class], [assembly_method], [null_on_null_input], [execute_as_principal_id] FROM sys.assembly_modules
Back to Top
sys.check_constraints
object that is a CHECK constraint, with sys.objects.type = 'C'.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | • | sysname | sys.objects | Object name. |
object_id | • | • | • | • | int | sys.objects | Object identification number. Is unique within a database. |
principal_id | • | • | • | • | int | sys.objects | 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 | sys.objects | 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 | sys.objects | ID of the object to which this object belongs. 0 = Not a child object. |
type | • | • | • | • | char(2) | sys.objects | 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) | sys.objects | 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 | sys.objects | Date the object was created. |
modify_date | • | • | • | • | datetime | sys.objects | 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 | sys.objects | Object is created by an internal SQL Server component. |
is_published | • | • | • | • | bit | sys.objects | Object is published. |
is_schema_published | • | • | • | • | bit | sys.objects | Only the schema of the object is published. |
is_disabled | • | • | • | • | bit | CHECK constraint is disabled. | |
is_not_for_replication | • | • | • | • | bit | CHECK constraint was created with the NOT FOR REPLICATION option. | |
is_not_trusted | • | • | • | • | bit | CHECK constraint has not been verified by the system for all rows. | |
parent_column_id | • | • | • | • | int | 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. |
|
definition | • | • | • | • | nvarchar(max) | SQL expression that defines this CHECK constraint. SQL Server 2005 differs from SQL Server 2000 in the way it decodes and stores SQL expressions in the catalog metadata. The semantics of the decoded expression are equivalent to the original text; however, there are no syntactic guarantees. For example, white spaces are removed from the decoded expression. For more information, see, Behavior Changes to Database Engine Features in SQL Server 20052. |
|
uses_database_collation | • | • | • | • | bit | 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. | |
is_system_named | • | • | • | • | bit | 1 = Name was generated by system. 0 = Name was supplied by the user. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_disabled], [is_not_for_replication], [is_not_trusted], [parent_column_id], [definition], [uses_database_collation], [is_system_named] FROM sys.check_constraints
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_disabled], [is_not_for_replication], [is_not_trusted], [parent_column_id], [definition], [uses_database_collation], [is_system_named] FROM sys.check_constraints
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_disabled], [is_not_for_replication], [is_not_trusted], [parent_column_id], [definition], [uses_database_collation], [is_system_named] FROM sys.check_constraints
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_disabled], [is_not_for_replication], [is_not_trusted], [parent_column_id], [definition], [uses_database_collation], [is_system_named] FROM sys.check_constraints
Back to Top
sys.columns
column of an object that has columns, such as views or tables. The following is a list of object types that have columns: Table-valued assembly functions (FT) Inline table-valued SQL functions (IF) Internal tables (IT) System tables (S) Table-valued SQL functions (TF) User tables (U) Views (V)
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object to which this column belongs. |
name | • | • | • | • | sysname | Name of the column. Is unique within the object. |
column_id | • | • | • | • | int | ID of the column. Is unique within the object. Column IDs might not be sequential. |
system_type_id | • | • | • | • | tinyint | ID of the system type of the column. |
user_type_id | • | • | • | • | int | ID of the type of the column as defined by the user. To return the name of the type, join to the sys.types1 catalog view on this column. |
max_length | • | • | • | • | smallint | Maximum length (in bytes) of the column. -1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml. For text columns, the max_length value will be 16 or the value set by sp_tableoption 'text in row'. |
precision | • | • | • | • | tinyint | Precision of the column if numeric-based; otherwise, 0. |
scale | • | • | • | • | tinyint | Scale of column if numeric-based; otherwise, 0. |
collation_name | • | • | • | • | sysname | Name of the collation of the column if character-based; otherwise, NULL. |
is_nullable | • | • | • | • | bit | 1 = Column is nullable. |
is_ansi_padded | • | • | • | • | bit | 1 = Column uses ANSI_PADDING ON behavior if character, binary, or variant. 0 = Column is not character, binary, or variant. |
is_rowguidcol | • | • | • | • | bit | 1 = Column is a declared ROWGUIDCOL. |
is_identity | • | • | • | • | bit | 1 = Column has identity values |
is_computed | • | • | • | • | bit | 1 = Column is a computed column. |
is_filestream | • | • | • | • | bit | Reserved for future use. |
is_replicated | • | • | • | • | bit | 1 = Column is replicated. |
is_non_sql_subscribed | • | • | • | • | bit | 1 = Column has a non-SQL Server subscriber. |
is_merge_published | • | • | • | • | bit | 1 = Column is merge-published. |
is_dts_replicated | • | • | • | • | bit | 1 = Column is replicated by using SQL Server 2005 Integration Services (SSIS). |
is_xml_document | • | • | • | • | bit | 1 = Content is a complete XML document. 0 = Content is a document fragment or the column data type is not xml. |
xml_collection_id | • | • | • | • | int | Nonzero if the data type of the column is xml and the XML is typed. The value will be the ID of the collection containing the validating XML schema namespace of the column. 0 = No XML schema collection. |
default_object_id | • | • | • | • | int | ID of the default object, regardless of whether it is a stand-alone object sys.sp_bindefault2, or an inline, column-level DEFAULT constraint. The parent_object_id column of an inline column-level default object is a reference back to the table itself. 0 = No default. |
rule_object_id | • | • | • | • | int | ID of the stand-alone rule bound to the column by using sys.sp_bindrule. 0 = No stand-alone rule. For column-level CHECK constraints, see sys.check_constraints (Transact-SQL)3. |
is_sparse | • | • | • | bit | 1 = Column is a sparse column. For more information, see Using Sparse Columns4. | |
is_column_set | • | • | • | bit | 1 = Column is a column set. For more information, see Using Sparse Columns4. |
TSQL
Sql 2005SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id] FROM sys.columns
Sql 2008
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set] FROM sys.columns
Sql 2008 R2
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set] FROM sys.columns
Sql 2012
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set] FROM sys.columns
Back to Top
sys.computed_columns
column found in sys.columns that is a computed-column.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
object_id | • | • | • | • | int | sys.columns | ID of the object to which this column belongs. |
name | • | • | • | • | sysname | sys.columns | Name of the column. Is unique within the object. |
column_id | • | • | • | • | int | sys.columns | ID of the column. Is unique within the object. Column IDs might not be sequential. |
system_type_id | • | • | • | • | tinyint | sys.columns | ID of the system type of the column. |
user_type_id | • | • | • | • | int | sys.columns | ID of the type of the column as defined by the user. To return the name of the type, join to the sys.types1 catalog view on this column. |
max_length | • | • | • | • | smallint | sys.columns | Maximum length (in bytes) of the column. -1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml. For text columns, the max_length value will be 16 or the value set by sp_tableoption 'text in row'. |
precision | • | • | • | • | tinyint | sys.columns | Precision of the column if numeric-based; otherwise, 0. |
scale | • | • | • | • | tinyint | sys.columns | Scale of column if numeric-based; otherwise, 0. |
collation_name | • | • | • | • | sysname | sys.columns | Name of the collation of the column if character-based; otherwise, NULL. |
is_nullable | • | • | • | • | bit | sys.columns | 1 = Column is nullable. |
is_ansi_padded | • | • | • | • | bit | sys.columns | 1 = Column uses ANSI_PADDING ON behavior if character, binary, or variant. 0 = Column is not character, binary, or variant. |
is_rowguidcol | • | • | • | • | bit | sys.columns | 1 = Column is a declared ROWGUIDCOL. |
is_identity | • | • | • | • | bit | sys.columns | 1 = Column has identity values |
is_computed | • | • | • | • | bit | sys.columns | 1 = Column is a computed column. |
is_filestream | • | • | • | • | bit | sys.columns | Reserved for future use. |
is_replicated | • | • | • | • | bit | sys.columns | 1 = Column is replicated. |
is_non_sql_subscribed | • | • | • | • | bit | sys.columns | 1 = Column has a non-SQL Server subscriber. |
is_merge_published | • | • | • | • | bit | sys.columns | 1 = Column is merge-published. |
is_dts_replicated | • | • | • | • | bit | sys.columns | 1 = Column is replicated by using SQL Server 2005 Integration Services (SSIS). |
is_xml_document | • | • | • | • | bit | sys.columns | 1 = Content is a complete XML document. 0 = Content is a document fragment or the column data type is not xml. |
xml_collection_id | • | • | • | • | int | sys.columns | Nonzero if the data type of the column is xml and the XML is typed. The value will be the ID of the collection containing the validating XML schema namespace of the column. 0 = No XML schema collection. |
default_object_id | • | • | • | • | int | sys.columns | ID of the default object, regardless of whether it is a stand-alone object sys.sp_bindefault2, or an inline, column-level DEFAULT constraint. The parent_object_id column of an inline column-level default object is a reference back to the table itself. 0 = No default. |
rule_object_id | • | • | • | • | int | sys.columns | ID of the stand-alone rule bound to the column by using sys.sp_bindrule. 0 = No stand-alone rule. For column-level CHECK constraints, see sys.check_constraints (Transact-SQL)3. |
definition | • | • | • | • | nvarchar(max) | SQL text that defines this computed-column. SQL Server 2005 differs from SQL Server 2000 in the way it decodes and stores SQL expressions in the catalog metadata. The semantics of the decoded expression are equivalent to the original text; however, there are no syntactic guarantees. For example, white spaces are removed from the decoded expression. For more information, see Behavior Changes to Database Engine Features in SQL Server 20052. |
|
uses_database_collation | • | • | • | • | bit | 1 = The column definition depends on the default collation of the database for correct evaluation; otherwise, 0. Such a dependency prevents changing the database default collation. | |
is_persisted | • | • | • | • | bit | Computed column is persisted. | |
is_sparse | • | • | • | bit | sys.columns | 1 = Column is a sparse column. For more information, see Using Sparse Columns4. | |
is_column_set | • | • | • | bit | sys.columns | 1 = Column is a column set. For more information, see Using Sparse Columns4. |
TSQL
Sql 2005SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [definition], [uses_database_collation], [is_persisted] FROM sys.computed_columns
Sql 2008
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set], [definition], [uses_database_collation], [is_persisted] FROM sys.computed_columns
Sql 2008 R2
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set], [definition], [uses_database_collation], [is_persisted] FROM sys.computed_columns
Sql 2012
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set], [definition], [uses_database_collation], [is_persisted] FROM sys.computed_columns
Back to Top
sys.default_constraints
object that is a default definition (created as part of a CREATE TABLE or ALTER TABLE statement instead of a CREATE DEFAULT statement), with sys.objects.type = D.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | • | sysname | sys.objects | Object name. |
object_id | • | • | • | • | int | sys.objects | Object identification number. Is unique within a database. |
principal_id | • | • | • | • | int | sys.objects | 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 | sys.objects | 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 | sys.objects | ID of the object to which this object belongs. 0 = Not a child object. |
type | • | • | • | • | char(2) | sys.objects | 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) | sys.objects | 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 | sys.objects | Date the object was created. |
modify_date | • | • | • | • | datetime | sys.objects | 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 | sys.objects | Object is created by an internal SQL Server component. |
is_published | • | • | • | • | bit | sys.objects | Object is published. |
is_schema_published | • | • | • | • | bit | sys.objects | Only the schema of the object is published. |
parent_column_id | • | • | • | • | int | ID of the column in parent_object_id to which this default belongs. | |
definition | • | • | • | • | nvarchar(max) | SQL expression that defines this default. SQL Server 2005 differs from SQL Server 2000 in the way it decodes and stores SQL expressions in the catalog metadata. The semantics of the decoded expression are equivalent to the original text; however, there are no syntactic guarantees. For example, white spaces are removed from the decoded expression. For more information, see, Behavior Changes to Database Engine Features in SQL Server 20052. |
|
is_system_named | • | • | • | • | bit | 1 = Name was generated by system. 0 = Name was supplied by the user. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [parent_column_id], [definition], [is_system_named] FROM sys.default_constraints
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [parent_column_id], [definition], [is_system_named] FROM sys.default_constraints
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [parent_column_id], [definition], [is_system_named] FROM sys.default_constraints
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [parent_column_id], [definition], [is_system_named] FROM sys.default_constraints
Back to Top
sys.event_notifications
object that is an event notification, with sys.objects.type = EN.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
name | • | • | • | • | sysname | Event notification name. |
object_id | • | • | • | • | int | Object identification number. Is unique within a database. |
parent_class | • | • | • | • | tinyint | Class of parent. 0 = Database 1 = Object or Column |
parent_class_desc | • | • | • | • | nvarchar(60) | DATABASE OBJECT_OR_COLUMN |
parent_id | • | • | • | • | int | Non-zero ID of the parent object. 0 = The parent class is the database. |
create_date | • | • | • | • | datetime | Date created. |
modify_date | • | • | • | • | datetime | Always equals create_date. |
service_name | • | • | • | • | nvarchar(256) | Name of the target service to which the notification is sent. |
broker_instance | • | • | • | • | nvarchar(128) | Broker instance to which the notification is sent. |
principal_id | • | • | • | • | int | ID of the database principal that owns this event notification. |
creator_sid | • | • | • | • | varbinary(85) | SID of the login who created the event notification. Is NULL if the FAN_IN option is not specified. |
TSQL
Sql 2005SELECT [name], [object_id], [parent_class], [parent_class_desc], [parent_id], [create_date], [modify_date], [service_name], [broker_instance], [principal_id], [creator_sid] FROM sys.event_notifications
Sql 2008
SELECT [name], [object_id], [parent_class], [parent_class_desc], [parent_id], [create_date], [modify_date], [service_name], [broker_instance], [principal_id], [creator_sid] FROM sys.event_notifications
Sql 2008 R2
SELECT [name], [object_id], [parent_class], [parent_class_desc], [parent_id], [create_date], [modify_date], [service_name], [broker_instance], [principal_id], [creator_sid] FROM sys.event_notifications
Sql 2012
SELECT [name], [object_id], [parent_class], [parent_class_desc], [parent_id], [create_date], [modify_date], [service_name], [broker_instance], [principal_id], [creator_sid] FROM sys.event_notifications
Back to Top
sys.events
event for which a trigger or event notification fires. These events represent the event types that are specified when the trigger or event notification is created by using CREATE TRIGGER1 or CREATE EVENT NOTIFICATION2.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the trigger or event notification. This value, together with type, uniquely identifies the row. |
type | • | • | • | • | int | Type of event that causes the trigger to fire. For a list of events, see the following table. |
type_desc | • | • | • | • | nvarchar(60) | Description of the type of event that causes a trigger to fire. For a list of events, see the following table. |
is_trigger_event | • | • | • | • | bit | 1 = Trigger event. 0 = Notification event. |
event_group_type | • | • | • | int | Event group on which the trigger or event notification is created, or null if not created on an event group. | |
event_group_type_desc | • | • | • | nvarchar(60) | Description of the event group on which the trigger or event notification is created, or null if not created on an event group. |
TSQL
Sql 2005SELECT [object_id], [type], [type_desc], [is_trigger_event] FROM sys.events
Sql 2008
SELECT [object_id], [type], [type_desc], [is_trigger_event], [event_group_type], [event_group_type_desc] FROM sys.events
Sql 2008 R2
SELECT [object_id], [type], [type_desc], [is_trigger_event], [event_group_type], [event_group_type_desc] FROM sys.events
Sql 2012
SELECT [object_id], [type], [type_desc], [is_trigger_event], [event_group_type], [event_group_type_desc] FROM sys.events
Back to Top
sys.extended_procedures
object that is an extended stored procedure, with sys.objects.type = X. Because extended stored procedures are installed into the master database, they are only visible from that database context. Selecting from the sys.extended_procedures view in any other database context will return an empty result set.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | • | sysname | sys.objects | Object name. |
object_id | • | • | • | • | int | sys.objects | Object identification number. Is unique within a database. |
principal_id | • | • | • | • | int | sys.objects | 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 | sys.objects | 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 | sys.objects | ID of the object to which this object belongs. 0 = Not a child object. |
type | • | • | • | • | char(2) | sys.objects | 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) | sys.objects | 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 | sys.objects | Date the object was created. |
modify_date | • | • | • | • | datetime | sys.objects | 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 | sys.objects | Object is created by an internal SQL Server component. |
is_published | • | • | • | • | bit | sys.objects | Object is published. |
is_schema_published | • | • | • | • | bit | sys.objects | Only the schema of the object is published. |
dll_name | • | • | • | • | nvarchar(260) | Name, including path, of the DLL for this extended stored procedure. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [dll_name] FROM sys.extended_procedures
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [dll_name] FROM sys.extended_procedures
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [dll_name] FROM sys.extended_procedures
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [dll_name] FROM sys.extended_procedures
Back to Top
sys.foreign_key_columns
column, or set of columns, that comprise a foreign key.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
constraint_object_id | • | • | • | • | int | ID of the FOREIGN KEY constraint |
constraint_column_id | • | • | • | • | int | ID of the column, or set of columns, that comprise the FOREIGN KEY (1..n where n=number of columns) |
parent_object_id | • | • | • | • | int | ID of the parent of the constraint, which is the referencing object. |
parent_column_id | • | • | • | • | int | ID of the parent column, which is the referencing column. |
referenced_object_id | • | • | • | • | int | ID of the referenced object, which has the candidate key. |
referenced_column_id | • | • | • | • | int | ID of the referenced column (candidate key column). |
TSQL
Sql 2005SELECT [constraint_object_id], [constraint_column_id], [parent_object_id], [parent_column_id], [referenced_object_id], [referenced_column_id] FROM sys.foreign_key_columns
Sql 2008
SELECT [constraint_object_id], [constraint_column_id], [parent_object_id], [parent_column_id], [referenced_object_id], [referenced_column_id] FROM sys.foreign_key_columns
Sql 2008 R2
SELECT [constraint_object_id], [constraint_column_id], [parent_object_id], [parent_column_id], [referenced_object_id], [referenced_column_id] FROM sys.foreign_key_columns
Sql 2012
SELECT [constraint_object_id], [constraint_column_id], [parent_object_id], [parent_column_id], [referenced_object_id], [referenced_column_id] FROM sys.foreign_key_columns
Back to Top
sys.foreign_keys
Contains a row per object that is a FOREIGN KEY constraint, with sys.object.type = F.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | • | sysname | sys.objects | Object name. |
object_id | • | • | • | • | int | sys.objects | Object identification number. Is unique within a database. |
principal_id | • | • | • | • | int | sys.objects | 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 | sys.objects | 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 | sys.objects | ID of the object to which this object belongs. 0 = Not a child object. |
type | • | • | • | • | char(2) | sys.objects | 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) | sys.objects | 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 | sys.objects | Date the object was created. |
modify_date | • | • | • | • | datetime | sys.objects | 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 | sys.objects | Object is created by an internal SQL Server component. |
is_published | • | • | • | • | bit | sys.objects | Object is published. |
is_schema_published | • | • | • | • | bit | sys.objects | Only the schema of the object is published. |
referenced_object_id | • | • | • | • | int | ID of the referenced object. | |
key_index_id | • | • | • | • | int | ID of the key index within the referenced object. | |
is_disabled | • | • | • | • | bit | FOREIGN KEY constraint is disabled. | |
is_not_for_replication | • | • | • | • | bit | FOREIGN KEY constraint was created by using the NOT FOR REPLICATION option. | |
is_not_trusted | • | • | • | • | bit | FOREIGN KEY constraint has not been verified by the system. | |
delete_referential_action | • | • | • | • | tinyint | The referential action that was declared for this FOREIGN KEY when a delete happens. 0 = No action 1 = Cascade 2 = Set null 3 = Set default |
|
delete_referential_action_desc | • | • | • | • | nvarchar(60) | Description of the referential action that was declared for this FOREIGN KEY when a delete occurs: NO_ACTION CASCADE SET_NULL SET_DEFAULT |
|
update_referential_action | • | • | • | • | tinyint | The referential action that was declared for this FOREIGN KEY when an update happens. 0 = No action 1 = Cascade 2 = Set null 3 = Set default |
|
update_referential_action_desc | • | • | • | • | nvarchar(60) | Description of the referential action that was declared for this FOREIGN KEY when an update happens: NO_ACTION CASCADE SET_NULL SET_DEFAULT |
|
is_system_named | • | • | • | • | bit | 1 = Name was generated by the system. 0 = Name was supplied by the user. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [referenced_object_id], [key_index_id], [is_disabled], [is_not_for_replication], [is_not_trusted], [delete_referential_action], [delete_referential_action_desc], [update_referential_action], [update_referential_action_desc], [is_system_named] FROM sys.foreign_keys
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [referenced_object_id], [key_index_id], [is_disabled], [is_not_for_replication], [is_not_trusted], [delete_referential_action], [delete_referential_action_desc], [update_referential_action], [update_referential_action_desc], [is_system_named] FROM sys.foreign_keys
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [referenced_object_id], [key_index_id], [is_disabled], [is_not_for_replication], [is_not_trusted], [delete_referential_action], [delete_referential_action_desc], [update_referential_action], [update_referential_action_desc], [is_system_named] FROM sys.foreign_keys
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [referenced_object_id], [key_index_id], [is_disabled], [is_not_for_replication], [is_not_trusted], [delete_referential_action], [delete_referential_action_desc], [update_referential_action], [update_referential_action_desc], [is_system_named] FROM sys.foreign_keys
Back to Top
sys.function_order_columns
Returns one row per column that is a part of an ORDER expression of a commmon language runtime (CLR) table-valued function.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | int | ID of the object (CLR table-valued function) the order is defined on. | |
order_column_id | • | • | • | int | ID of the order column. order_column_id is unique only within object_id. order_column_id represents the position of this column in the ordering. |
|
column_id | • | • | • | int | ID of the column in object_id. column_id is unique only within object_id. |
|
is_descending | • | • | • | bit | 1 = order column has a descending sort direction. 0 = order column has an ascending sort direction. |
TSQL
Sql 2008SELECT [object_id], [order_column_id], [column_id], [is_descending] FROM sys.function_order_columns
Sql 2008 R2
SELECT [object_id], [order_column_id], [column_id], [is_descending] FROM sys.function_order_columns
Sql 2012
SELECT [object_id], [order_column_id], [column_id], [is_descending] FROM sys.function_order_columns
Back to Top
sys.functions
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.triggers1. sys.triggers supports a mixture of name-scoping rules for the various kinds of triggers.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | 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. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.functions
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.functions
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.functions
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.functions
Back to Top
sys.identity_columns
column that is an identity column.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
object_id | • | • | • | • | int | sys.columns | ID of the object to which this column belongs. |
name | • | • | • | • | sysname | sys.columns | Name of the column. Is unique within the object. |
column_id | • | • | • | • | int | sys.columns | ID of the column. Is unique within the object. Column IDs might not be sequential. |
system_type_id | • | • | • | • | tinyint | sys.columns | ID of the system type of the column. |
user_type_id | • | • | • | • | int | sys.columns | ID of the type of the column as defined by the user. To return the name of the type, join to the sys.types1 catalog view on this column. |
max_length | • | • | • | • | smallint | sys.columns | Maximum length (in bytes) of the column. -1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml. For text columns, the max_length value will be 16 or the value set by sp_tableoption 'text in row'. |
precision | • | • | • | • | tinyint | sys.columns | Precision of the column if numeric-based; otherwise, 0. |
scale | • | • | • | • | tinyint | sys.columns | Scale of column if numeric-based; otherwise, 0. |
collation_name | • | • | • | • | sysname | sys.columns | Name of the collation of the column if character-based; otherwise, NULL. |
is_nullable | • | • | • | • | bit | sys.columns | 1 = Column is nullable. |
is_ansi_padded | • | • | • | • | bit | sys.columns | 1 = Column uses ANSI_PADDING ON behavior if character, binary, or variant. 0 = Column is not character, binary, or variant. |
is_rowguidcol | • | • | • | • | bit | sys.columns | 1 = Column is a declared ROWGUIDCOL. |
is_identity | • | • | • | • | bit | sys.columns | 1 = Column has identity values |
is_computed | • | • | • | • | bit | sys.columns | 1 = Column is a computed column. |
is_filestream | • | • | • | • | bit | sys.columns | Reserved for future use. |
is_replicated | • | • | • | • | bit | sys.columns | 1 = Column is replicated. |
is_non_sql_subscribed | • | • | • | • | bit | sys.columns | 1 = Column has a non-SQL Server subscriber. |
is_merge_published | • | • | • | • | bit | sys.columns | 1 = Column is merge-published. |
is_dts_replicated | • | • | • | • | bit | sys.columns | 1 = Column is replicated by using SQL Server 2005 Integration Services (SSIS). |
is_xml_document | • | • | • | • | bit | sys.columns | 1 = Content is a complete XML document. 0 = Content is a document fragment or the column data type is not xml. |
xml_collection_id | • | • | • | • | int | sys.columns | Nonzero if the data type of the column is xml and the XML is typed. The value will be the ID of the collection containing the validating XML schema namespace of the column. 0 = No XML schema collection. |
default_object_id | • | • | • | • | int | sys.columns | ID of the default object, regardless of whether it is a stand-alone object sys.sp_bindefault2, or an inline, column-level DEFAULT constraint. The parent_object_id column of an inline column-level default object is a reference back to the table itself. 0 = No default. |
rule_object_id | • | • | • | • | int | sys.columns | ID of the stand-alone rule bound to the column by using sys.sp_bindrule. 0 = No stand-alone rule. For column-level CHECK constraints, see sys.check_constraints (Transact-SQL)3. |
seed_value | • | • | • | • | sql_variant | Seed value for this identity column. The data type of the seed value is the same as the data type of the column itself. | |
increment_value | • | • | • | • | sql_variant | Increment value for this identity column. The data type of the seed value is the same as the data type of the column itself. | |
last_value | • | • | • | • | sql_variant | Last value generated for this identity column. The data type of the seed value is the same as the data type of the column itself. | |
is_not_for_replication | • | • | • | • | bit | Identity column is declared NOT FOR REPLICATION. | |
is_sparse | • | • | • | bit | sys.columns | 1 = Column is a sparse column. For more information, see Using Sparse Columns4. | |
is_column_set | • | • | • | bit | sys.columns | 1 = Column is a column set. For more information, see Using Sparse Columns4. |
TSQL
Sql 2005SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [seed_value], [increment_value], [last_value], [is_not_for_replication] FROM sys.identity_columns
Sql 2008
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set], [seed_value], [increment_value], [last_value], [is_not_for_replication] FROM sys.identity_columns
Sql 2008 R2
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set], [seed_value], [increment_value], [last_value], [is_not_for_replication] FROM sys.identity_columns
Sql 2012
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set], [seed_value], [increment_value], [last_value], [is_not_for_replication] FROM sys.identity_columns
Back to Top
sys.index_columns
column that is part of a sys.indexes index or unordered table (heap).
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object the index is defined on. |
index_id | • | • | • | • | int | ID of the index in which the column is defined. |
index_column_id | • | • | • | • | int | ID of the index column. index_column_id is unique only within index_id. |
column_id | • | • | • | • | int | ID of the column in object_id. 0 = Row Identifier (RID) in a nonclustered index. column_id is unique only within object_id. |
key_ordinal | • | • | • | • | tinyint | Ordinal (1-based) within set of key-columns. 0 = Not a key column, or is an XML index. Columns of type xml are not comparable, so an XML index does not induce an ordering on the underlying column values. Since an XML index is, therefore not a key, the key_ordinal value will always be 0. |
partition_ordinal | • | • | • | • | tinyint | Ordinal (1-based) within set of partitioning columns. 0 = Not a partitioning column. |
is_descending_key | • | • | • | • | bit | 1 = Index key column has a descending sort direction. 0 = Index key column has an ascending sort direction. |
is_included_column | • | • | • | • | bit | 1 = Column is a nonkey column added to the index by using the CREATE INDEX INCLUDE clause. 0 = Column is not an included column. |
distribution_ordinal | • | bit | Reserved for future use. |
TSQL
Sql 2005SELECT [object_id], [index_id], [index_column_id], [column_id], [key_ordinal], [partition_ordinal], [is_descending_key], [is_included_column] FROM sys.index_columns
Sql 2008
SELECT [object_id], [index_id], [index_column_id], [column_id], [key_ordinal], [partition_ordinal], [is_descending_key], [is_included_column] FROM sys.index_columns
Sql 2008 R2
SELECT [object_id], [index_id], [index_column_id], [column_id], [key_ordinal], [partition_ordinal], [is_descending_key], [is_included_column] FROM sys.index_columns
Sql 2012
SELECT [object_id], [index_id], [index_column_id], [column_id], [key_ordinal], [partition_ordinal], [distribution_ordinal], [is_descending_key], [is_included_column] FROM sys.index_columns
Back to Top
sys.indexes
Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object to which this index belongs. |
name | • | • | • | • | sysname | Name of the index. name is unique only within the object. NULL = Heap |
index_id | • | • | • | • | int | ID of the index. index_id is unique only within the object. 0 = Heap 1 = Clustered index > 1 = Nonclustered index |
type | • | • | • | • | tinyint | Type of index: 0 = Heap 1 = Clustered 2 = Nonclustered 3 = XML |
type_desc | • | • | • | • | nvarchar(60) | Description of index type: HEAP CLUSTERED NONCLUSTERED XML |
is_unique | • | • | • | • | bit | 1 = Index is unique. 0 = Index is not unique. |
data_space_id | • | • | • | • | int | ID of the data space for this index. Data space is either a filegroup or partition scheme. 0 = object_id is a table-valued function. |
ignore_dup_key | • | • | • | • | bit | 1 = IGNORE_DUP_KEY is ON. 0 = IGNORE_DUP_KEY is OFF. |
is_primary_key | • | • | • | • | bit | 1 = Index is part of a PRIMARY KEY constraint. |
is_unique_constraint | • | • | • | • | bit | 1 = Index is part of a UNIQUE constraint. |
fill_factor | • | • | • | • | tinyint | > 0 = FILLFACTOR percentage used when the index was created or rebuilt. 0 = Default value |
is_padded | • | • | • | • | bit | 1 = PADINDEX is ON. 0 = PADINDEX is OFF. |
is_disabled | • | • | • | • | bit | 1 = Index is disabled. 0 = Index is not disabled. |
is_hypothetical | • | • | • | • | bit | 1 = Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics. 0 = Index is not hypothetical. |
allow_row_locks | • | • | • | • | bit | 1 = Index allows row locks. 0 = Index does not allow row locks. |
allow_page_locks | • | • | • | • | bit | 1 = Index allows page locks. 0 = Index does not allow page locks. |
has_filter | • | • | • | bit | 1 = Index has a filter and only contains rows that satisfy the filter definition. 0 = Index does not have a filter. |
|
filter_definition | • | • | • | nvarchar(max) | Expression for the subset of rows included in the filtered index. NULL for heap or non-filtered index. |
TSQL
Sql 2005SELECT [object_id], [name], [index_id], [type], [type_desc], [is_unique], [data_space_id], [ignore_dup_key], [is_primary_key], [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [is_hypothetical], [allow_row_locks], [allow_page_locks] FROM sys.indexes
Sql 2008
SELECT [object_id], [name], [index_id], [type], [type_desc], [is_unique], [data_space_id], [ignore_dup_key], [is_primary_key], [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [is_hypothetical], [allow_row_locks], [allow_page_locks], [has_filter], [filter_definition] FROM sys.indexes
Sql 2008 R2
SELECT [object_id], [name], [index_id], [type], [type_desc], [is_unique], [data_space_id], [ignore_dup_key], [is_primary_key], [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [is_hypothetical], [allow_row_locks], [allow_page_locks], [has_filter], [filter_definition] FROM sys.indexes
Sql 2012
SELECT [object_id], [name], [index_id], [type], [type_desc], [is_unique], [data_space_id], [ignore_dup_key], [is_primary_key], [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [is_hypothetical], [allow_row_locks], [allow_page_locks], [has_filter], [filter_definition] FROM sys.indexes
Back to Top
sys.internal_tables
object that is an internal table. Internal tables are automatically generated by SQL Server to support various features. For example, when you create a primary XML index, SQL Server automatically creates an internal table to persist the shredded XML document data. Internal tables appear in the sys schema of every database and have unique, system-generated names that indicate their function, for example, xml_index_nodes_2021582240_32001 or queue_messages_1977058079
Internal tables do not contain user-accessible data, and their schema are fixed and unalterable. You cannot reference internal table names in Transact-SQL statements. For example, you cannot execute a statement such as SELECT * FROM
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | • | sysname | sys.objects | Object name. |
object_id | • | • | • | • | int | sys.objects | Object identification number. Is unique within a database. |
principal_id | • | • | • | • | int | sys.objects | 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 | sys.objects | 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 | sys.objects | ID of the object to which this object belongs. 0 = Not a child object. |
type | • | • | • | • | char(2) | sys.objects | 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) | sys.objects | 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 | sys.objects | Date the object was created. |
modify_date | • | • | • | • | datetime | sys.objects | 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 | sys.objects | Object is created by an internal SQL Server component. |
is_published | • | • | • | • | bit | sys.objects | Object is published. |
is_schema_published | • | • | • | • | bit | sys.objects | Only the schema of the object is published. |
internal_type | • | • | • | • | tinyint | Type of the internal table: 201 = queue_messages 202 = xml_index_nodes 203 = fulltext_catalog_freelist 204 = fulltext_catalog_map 205 = query_notification 206 = service_broker_map |
|
internal_type_desc | • | • | • | • | nvarchar(60) | Description of the type of internal table: QUEUE_MESSAGES XML_INDEX_NODES FULLTEXT_CATALOG_FREELIST FULLTEXT_CATALOG_MAP QUERY_NOTIFICATION SERVICE_BROKER_MAP |
|
parent_id | • | • | • | • | int | ID of the parent, regardless of whether it is schema-scoped or not. Otherwise, 0 if there is no parent. queue_messages = object_id of queue xml_index_nodes = object_id of the xml index fulltext_catalog_freelist = fulltext_catalog_id of the full-text catalog fulltext_index_map = object_id of the full-text index query_notification, or service_broker_map = 0 |
|
parent_minor_id | • | • | • | • | int | Minor ID of the parent. xml_index_nodes = index_id of the XML index queue_messages, fulltext_catalog_freelist, fulltext_index_map, query_notification, or service_broker_map = 0 |
|
lob_data_space_id | • | • | • | • | int | Non-zero value is the ID of data space (filegroup or partition-scheme) that holds the large object (LOB) data for this table. | |
filestream_data_space_id | • | • | • | • | int | Reserved for future use. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [internal_type], [internal_type_desc], [parent_id], [parent_minor_id], [lob_data_space_id], [filestream_data_space_id] FROM sys.internal_tables
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [internal_type], [internal_type_desc], [parent_id], [parent_minor_id], [lob_data_space_id], [filestream_data_space_id] FROM sys.internal_tables
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [internal_type], [internal_type_desc], [parent_id], [parent_minor_id], [lob_data_space_id], [filestream_data_space_id] FROM sys.internal_tables
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [internal_type], [internal_type_desc], [parent_id], [parent_minor_id], [lob_data_space_id], [filestream_data_space_id] FROM sys.internal_tables
Back to Top
sys.key_constraints
object that is a primary key or unique constraint. Includes sys.objects.type PK and UQ.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | • | sysname | sys.objects | Object name. |
object_id | • | • | • | • | int | sys.objects | Object identification number. Is unique within a database. |
principal_id | • | • | • | • | int | sys.objects | 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 | sys.objects | 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 | sys.objects | ID of the object to which this object belongs. 0 = Not a child object. |
type | • | • | • | • | char(2) | sys.objects | 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) | sys.objects | 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 | sys.objects | Date the object was created. |
modify_date | • | • | • | • | datetime | sys.objects | 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 | sys.objects | Object is created by an internal SQL Server component. |
is_published | • | • | • | • | bit | sys.objects | Object is published. |
is_schema_published | • | • | • | • | bit | sys.objects | Only the schema of the object is published. |
unique_index_id | • | • | • | • | int | ID of the corresponding unique index in the parent object that was created to enforce this constraint. | |
is_system_named | • | • | • | • | bit | 1 = Name was generated by system. 0 = Name was supplied by the user. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [unique_index_id], [is_system_named] FROM sys.key_constraints
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [unique_index_id], [is_system_named] FROM sys.key_constraints
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [unique_index_id], [is_system_named] FROM sys.key_constraints
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [unique_index_id], [is_system_named] FROM sys.key_constraints
Back to Top
sys.numbered_procedure_parameters
Contains one row for each parameter of a numbered procedure. When you create a numbered stored procedure, the base procedure is number 1. All subsequent procedures have numbers 2, 3, and so forth. sys.numbered_procedure_parameters contains the parameter definitions for all subsequent procedures, numbered 2 and greater. This view does not show parameters for the base stored procedure (number = 1). The base stored procedure is similar to a nonnumbered stored procedure. Therefore, its parameters are represented in sys.parameters (Transact-SQL)1. 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. Note XML and CLR parameters are not supported for numbered procedures.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object to which this parameter belongs. |
procedure_number | • | • | • | • | smallint | Number of this procedure within the object, 2 or greater. |
name | • | • | • | • | sysname | Name of the parameter. Is unique within procedure_number. |
parameter_id | • | • | • | • | int | ID of the parameter. Is unique within the procedure_number. |
system_type_id | • | • | • | • | tinyint | ID of the system type of the parameter |
user_type_id | • | • | • | • | int | ID of the type, as defined by user, of the parameter. |
max_length | • | • | • | • | smallint | Maximum length of the parameter in bytes. -1 = Column data type is varchar(max), nvarchar(max), or varbinary(max). |
precision | • | • | • | • | tinyint | Precision of the parameter if numeric-based; otherwise, 0. |
scale | • | • | • | • | tinyint | Scale of the parameter if numeric-based; otherwise, 0. |
is_output | • | • | • | • | bit | 1 = Parameter is output or return; otherwise, 0 |
is_cursor_ref | • | • | • | • | bit | 1 = Parameter is a cursor-reference parameter. |
TSQL
Sql 2005SELECT [object_id], [procedure_number], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref] FROM sys.numbered_procedure_parameters
Sql 2008
SELECT [object_id], [procedure_number], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref] FROM sys.numbered_procedure_parameters
Sql 2008 R2
SELECT [object_id], [procedure_number], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref] FROM sys.numbered_procedure_parameters
Sql 2012
SELECT [object_id], [procedure_number], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref] FROM sys.numbered_procedure_parameters
Back to Top
sys.numbered_procedures
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 sys.objects and sys.procedures. 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.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object of the stored procedure. |
procedure_number | • | • | • | • | smallint | Number of this procedure within the object, 2 or greater. |
definition | • | • | • | • | nvarchar(max) | The SQL Server text that defines this procedure. NULL = encrypted. |
TSQL
Sql 2005SELECT [object_id], [procedure_number], [definition] FROM sys.numbered_procedures
Sql 2008
SELECT [object_id], [procedure_number], [definition] FROM sys.numbered_procedures
Sql 2008 R2
SELECT [object_id], [procedure_number], [definition] FROM sys.numbered_procedures
Sql 2012
SELECT [object_id], [procedure_number], [definition] FROM sys.numbered_procedures
Back to Top
sys.objects
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.triggers1. sys.triggers supports a mixture of name-scoping rules for the various kinds of triggers.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | 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. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.objects
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.objects
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.objects
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.objects
Back to Top
sys.parameters
parameter of an object that accepts parameters. If the object is a scalar function, there is also a single row describing the return value. That row will have a parameter_id value of 0.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object to which this parameter belongs. |
name | • | • | • | • | sysname | Name of the parameter. Is unique within the object. If the object is a scalar function, the parameter name is an empty string in the row representing the return value. |
parameter_id | • | • | • | • | int | ID of the parameter. Is unique within the object. If the object is a scalar function, parameter_id = 0 represents the return value. |
system_type_id | • | • | • | • | tinyint | ID of the system type of the parameter. |
user_type_id | • | • | • | • | int | ID of the type of the parameter as defined by the user. To return the name of the type, join to the sys.types1 catalog view on this column. |
max_length | • | • | • | • | smallint | Maximum length of the parameter, in bytes. Value = -1 when the column data type is varchar(max), nvarchar(max), varbinary(max), or xml. |
precision | • | • | • | • | tinyint | Precision of the parameter if numeric-based; otherwise, 0. |
scale | • | • | • | • | tinyint | Scale of the parameter if numeric-based; otherwise, 0. |
is_output | • | • | • | • | bit | 1 = Parameter is output or return; otherwise, 0. |
is_cursor_ref | • | • | • | • | bit | 1 = Parameter is a cursor-reference parameter. |
has_default_value | • | • | • | • | bit | 1 = Parameter has default value. SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules2 catalog view, or use the OBJECT_DEFINITION3 system function. |
is_xml_document | • | • | • | • | bit | 1 = Content is a complete XML document. 0 = Content is a document fragment, or the data type of the column is not xml. |
default_value | • | • | • | • | sql_variant | If has_default_value is 1, the value of this column is the value of the default for the parameter; otherwise, NULL. |
xml_collection_id | • | • | • | • | int | Non-zero if the data type of the parameter is xml and the XML is typed. The value is the ID of the collection containing the validating XML schema namespace of the parameter Is 0 if there is no XML schema collection. |
is_readonly | • | • | • | bit | 1 = Parameter is READONLY; otherwise, 0. |
TSQL
Sql 2005SELECT [object_id], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref], [has_default_value], [is_xml_document], [default_value], [xml_collection_id] FROM sys.parameters
Sql 2008
SELECT [object_id], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref], [has_default_value], [is_xml_document], [default_value], [xml_collection_id], [is_readonly] FROM sys.parameters
Sql 2008 R2
SELECT [object_id], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref], [has_default_value], [is_xml_document], [default_value], [xml_collection_id], [is_readonly] FROM sys.parameters
Sql 2012
SELECT [object_id], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref], [has_default_value], [is_xml_document], [default_value], [xml_collection_id], [is_readonly] FROM sys.parameters
Back to Top
sys.partitions
partition of all the tables and most types of indexes in the database. Special index types like Full-Text, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
partition_id | • | • | • | • | bigint | ID of the partition. Is unique within a database. |
object_id | • | • | • | • | int | ID of the object to which this partition belongs. Every table or view is comprised of at least one partition. |
index_id | • | • | • | • | int | ID of the index within the object to which this partition belongs. |
partition_number | • | • | • | • | int | 1-based partition number within the owning index or heap. For nonpartitioned tables and indexes, the value of this column is 1. |
hobt_id | • | • | • | • | bigint | ID of the data heap or B-tree that contains the rows for this partition. |
rows | • | • | • | • | bigint | Approximate number of rows in this partition. |
data_compression | • | • | • | tinyint | Indicates the state of compression for each partition: 0 = NONE 1 = ROW 2 = PAGE Note Full text indexes will be compressed in any edition of SQL Server. |
|
data_compression_desc | • | • | • | nvarchar(60) | Indicates the state of compression for each partition. Possible values are NONE, ROW, and PAGE. | |
filestream_filegroup_id | • | smallint | Indicates the ID of the FILESTREAM filegroup stored on this partition. |
TSQL
Sql 2005SELECT [partition_id], [object_id], [index_id], [partition_number], [hobt_id], [rows] FROM sys.partitions
Sql 2008
SELECT [partition_id], [object_id], [index_id], [partition_number], [hobt_id], [rows], [data_compression], [data_compression_desc] FROM sys.partitions
Sql 2008 R2
SELECT [partition_id], [object_id], [index_id], [partition_number], [hobt_id], [rows], [data_compression], [data_compression_desc] FROM sys.partitions
Sql 2012
SELECT [partition_id], [object_id], [index_id], [partition_number], [hobt_id], [rows], [filestream_filegroup_id], [data_compression], [data_compression_desc] FROM sys.partitions
Back to Top
sys.procedures
object that is a procedure of some kind, with sys.objects.type = P, X, RF, and PC.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | • | sysname | sys.objects | Object name. |
object_id | • | • | • | • | int | sys.objects | Object identification number. Is unique within a database. |
principal_id | • | • | • | • | int | sys.objects | 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 | sys.objects | 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 | sys.objects | ID of the object to which this object belongs. 0 = Not a child object. |
type | • | • | • | • | char(2) | sys.objects | 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) | sys.objects | 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 | sys.objects | Date the object was created. |
modify_date | • | • | • | • | datetime | sys.objects | 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 | sys.objects | Object is created by an internal SQL Server component. |
is_published | • | • | • | • | bit | sys.objects | Object is published. |
is_schema_published | • | • | • | • | bit | sys.objects | Only the schema of the object is published. |
is_auto_executed | • | • | • | • | bit | 1 = Procedure is auto-executed at the server startup; otherwise, 0. Can only be set for procedures in the master database. | |
is_execution_replicated | • | • | • | • | bit | Execution of this procedure is replicated. | |
is_repl_serializable_only | • | • | • | • | bit | Replication of the procedure execution is done only when the transaction can be serialized. | |
skips_repl_constraints | • | • | • | • | bit | During execution, the procedure skips constraints marked NOT FOR REPLICATION. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_auto_executed], [is_execution_replicated], [is_repl_serializable_only], [skips_repl_constraints] FROM sys.procedures
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_auto_executed], [is_execution_replicated], [is_repl_serializable_only], [skips_repl_constraints] FROM sys.procedures
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_auto_executed], [is_execution_replicated], [is_repl_serializable_only], [skips_repl_constraints] FROM sys.procedures
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_auto_executed], [is_execution_replicated], [is_repl_serializable_only], [skips_repl_constraints] FROM sys.procedures
Back to Top
sys.rules
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.triggers1. sys.triggers supports a mixture of name-scoping rules for the various kinds of triggers.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | 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. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.rules
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.rules
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.rules
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.rules
Back to Top
sys.sql_dependencies
dependency on a referenced entity as referenced in the Transact-SQL expression or statements that define some other referencing object. Important This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.sql_expression_dependencies1 instead.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
class | • | • | • | • | tinyint | Identifies the class of the referenced (independent) entity: 0 = Object or column (non-schema-bound references only) 1 = Object or column (schema-bound references) 2 = Types (schema-bound references) 3 = XML Schema collections (schema-bound references) 4 = Partition function (schema-bound references) |
class_desc | • | • | • | • | nvarchar(60) | Description of class of referenced (independent) entity: OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND OBJECT_OR_COLUMN_REFERENCE_SCHEMA_BOUND TYPE_REFERENCE XML_SCHEMA_COLLECTION_REFERENCE PARTITION_FUNCTION_REFERENCE |
object_id | • | • | • | • | int | ID of the referencing (dependent) object. |
column_id | • | • | • | • | int | If the dependent ID is a column, ID of referencing (dependent) column; otherwise, 0. |
referenced_major_id | • | • | • | • | int | ID of the referenced (independent) entity, interpreted by value of class, according to: 0, 1 = Object ID of object or column. 2 = Type ID. 3 = XML Schema collection ID. |
referenced_minor_id | • | • | • | • | int | Minor-ID of the referenced (independent) entity, interpreted by value of class, as shown in the following. When class =: 0, referenced_minor_id is a column ID; or if not a column, it is 0. 1, referenced_minor_id is a column ID; or if not a column, it is 0. Otherwise, referenced_minor_id = 0. |
is_selected | • | • | • | • | bit | Object or column is selected. |
is_updated | • | • | • | • | bit | Object or column is updated. |
is_select_all | • | • | • | • | bit | Object is used in SELECT * statement (object-level only). |
TSQL
Sql 2005SELECT [class], [class_desc], [object_id], [column_id], [referenced_major_id], [referenced_minor_id], [is_selected], [is_updated], [is_select_all] FROM sys.sql_dependencies
Sql 2008
SELECT [class], [class_desc], [object_id], [column_id], [referenced_major_id], [referenced_minor_id], [is_selected], [is_updated], [is_select_all] FROM sys.sql_dependencies
Sql 2008 R2
SELECT [class], [class_desc], [object_id], [column_id], [referenced_major_id], [referenced_minor_id], [is_selected], [is_updated], [is_select_all] FROM sys.sql_dependencies
Sql 2012
SELECT [class], [class_desc], [object_id], [column_id], [referenced_major_id], [referenced_minor_id], [is_selected], [is_updated], [is_select_all] FROM sys.sql_dependencies
Back to Top
sys.sql_expression_dependencies
Contains one row for each by-name dependency on a user-defined entity in the current database. A dependency between two entities is created when one entity, called the referenced entity, appears by name in a persisted SQL expression of another entity, called the referencing entity. For example, when a table is referenced in the definition of a view, the view, as the referencing entity, depends on the table, the referenced entity. If the table is dropped, the view is unusable. You can use this catalog view to report dependency information for the following entities: Schema-bound entities. Non-schema-bound entities. Cross-database and cross-server entities. Entity names are reported; however, entity IDs are not resolved. Column-level dependencies on schema-bound entities. Column-level dependencies for non-schema-bound objects can be returned by using sys.dm_sql_referenced_entities1. Server-level DDL triggers when in the context of the master database.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
referencing_id | • | • | • | int | ID of the referencing entity. Is not nullable. | |
referencing_minor_id | • | • | • | int | Column ID when the referencing entity is a column; otherwise 0. Is not nullable. | |
referencing_class | • | • | • | tinyint | Class of the referencing entity. 1 = Object or column 12 = Database DDL trigger 13 = Server DDL trigger Is not nullable. |
|
referencing_class_desc | • | • | • | nvarchar(60) | Description of the class of referencing entity. OBJECT_OR_COLUMN DATABASE_DDL_TRIGGER SERVER_DDL_TRIGGER Is not nullable. |
|
is_schema_bound_reference | • | • | • | bit | 1 = Referenced entity is schema-bound. 0 = Referenced entity is non-schema-bound. Is not nullable. |
|
referenced_class | • | • | • | tinyint | Class of the referenced entity. 1 = Object or column 6 = Type 10 = XML schema collection 21 = Partition function Is not nullable. |
|
referenced_class_desc | • | • | • | nvarchar(60) | Description of class of referenced entity. OBJECT_OR_COLUMN TYPE XML_SCHEMA_COLLECTION PARTITION_FUNCTION Is not nullable. |
|
referenced_server_name | • | • | • | sysname | Name of the server of the referenced entity. This column is populated for cross-server dependencies that are made by specifying a valid four-part name. For information about multipart names, see Transact-SQL Syntax Conventions (Transact-SQL)2. NULL for non-schema-bound entities for which the entity was referenced without specifying a four-part name. NULL for schema-bound entities because they must be in the same database and therefore can only be defined using a two-part (schema.object) name. |
|
referenced_database_name | • | • | • | sysname | Name of the database of the referenced entity. This column is populated for cross-database or cross-server references that are made by specifying a valid three-part or four-part name. NULL for non-schema-bound references when specified using a one-part or two-part name. NULL for schema-bound entities because they must be in the same database and therefore can only be defined using a two-part (schema.object) name. |
|
referenced_schema_name | • | • | • | sysname | Schema in which the referenced entity belongs. NULL for non-schema-bound references in which the entity was referenced without specifying the schema name. Never NULL for schema-bound references because schema-bound entities must be defined and referenced by using a two-part name. |
|
referenced_entity_name | • | • | • | sysname | Name of the referenced entity. Is not nullable. | |
referenced_id | • | • | • | int | ID of the referenced entity. Always NULL for cross-server and cross-database references. NULL for references within the database if the ID cannot be determined. For non-schema-bound references, the ID cannot be resolved in the following cases: The referenced entity does not exist in the database. The schema of the referenced entity depends on the schema of the caller and is resolved at run time. In this case, is_caller_dependent is set to 1. Never NULL for schema-bound references. |
|
referenced_minor_id | • | • | • | int | ID of the referenced column when the referencing entity is a column; otherwise 0. Is not nullable. A referenced entity is a column when a column is identified by name in the referencing entity, or when the parent entity is used in a SELECT * statement. |
|
is_caller_dependent | • | • | • | bit | Indicates that schema binding for the referenced entity occurs at runtime; therefore, resolution of the entity ID depends on the schema of the caller. This occurs when the referenced entity is a stored procedure, extended stored procedure, or a non-schema-bound user-defined function called in an EXECUTE statement. 1 = The referenced entity is caller dependent and is resolved at runtime. In this case, referenced_id is NULL. 0 = The referenced entity ID is not caller dependent. Always 0 for schema-bound references and for cross-database and cross-server references that explicitly specify a schema name. For example, a reference to an entity in the format EXEC MyDatabase.MySchema.MyProc is not caller dependent. However, a reference in the format EXEC MyDatabase..MyProc is caller dependent. |
|
is_ambiguous | • | • | • | bit | Indicates the reference is ambiguous and can resolve at run time to a user-defined function, a user-defined type (UDT), or an xquery reference to a column of type xml. For example, assume that the statement SELECT Sales.GetOrder() FROM Sales.MySales is defined in a stored procedure. Until the stored procedure is executed, it is not known whether Sales.GetOrder() is a user-defined function in the Sales schema or column named Sales of type UDT with a method named GetOrder(). 1 = Reference is ambiguous. 0 = Reference is unambiguous or the entity can be successfully bound when the view is called. Always 0 for schema bound references. |
TSQL
Sql 2008SELECT [referencing_id], [referencing_minor_id], [referencing_class], [referencing_class_desc], [is_schema_bound_reference], [referenced_class], [referenced_class_desc], [referenced_server_name], [referenced_database_name], [referenced_schema_name], [referenced_entity_name], [referenced_id], [referenced_minor_id], [is_caller_dependent], [is_ambiguous] FROM sys.sql_expression_dependencies
Sql 2008 R2
SELECT [referencing_id], [referencing_minor_id], [referencing_class], [referencing_class_desc], [is_schema_bound_reference], [referenced_class], [referenced_class_desc], [referenced_server_name], [referenced_database_name], [referenced_schema_name], [referenced_entity_name], [referenced_id], [referenced_minor_id], [is_caller_dependent], [is_ambiguous] FROM sys.sql_expression_dependencies
Sql 2012
SELECT [referencing_id], [referencing_minor_id], [referencing_class], [referencing_class_desc], [is_schema_bound_reference], [referenced_class], [referenced_class_desc], [referenced_server_name], [referenced_database_name], [referenced_schema_name], [referenced_entity_name], [referenced_id], [referenced_minor_id], [is_caller_dependent], [is_ambiguous] FROM sys.sql_expression_dependencies
Back to Top
sys.sql_modules
object that is an SQL language-defined module. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objects1 catalog view.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object of the containing object. Is unique within a database. |
definition | • | • | • | • | nvarchar(max) | SQL text that defines this module. NULL = Encrypted. |
uses_ansi_nulls | • | • | • | • | bit | Module was created with SET ANSI_NULLS ON. Will always be = 0 for rules and defaults. |
uses_quoted_identifier | • | • | • | • | bit | Module was created with SET QUOTED_IDENTIFIER ON. |
is_schema_bound | • | • | • | • | bit | Module was created with SCHEMABINDING option. |
uses_database_collation | • | • | • | • | bit | 1 = Schema-bound module definition depends on the default-collation of the database for correct evaluation; otherwise, 0. Such a dependency prevents changing the database's default collation. |
is_recompiled | • | • | • | • | bit | Procedure was created WITH RECOMPILE option. |
null_on_null_input | • | • | • | • | bit | Module was declared to produce a NULL output on any NULL input. |
execute_as_principal_id | • | • | • | • | int | ID of the EXECUTE AS database principal. NULL by default or if EXECUTE AS CALLER. ID of the specified principal if EXECUTE AS SELF or EXECUTE AS -2 = EXECUTE AS OWNER. |
is_contained | • | bit | Indicates if a module in a contained database is contained. 0 = The module is not contained. 1 = The module is contained. Does not apply to SQL Azure. |
TSQL
Sql 2005SELECT [object_id], [definition], [uses_ansi_nulls], [uses_quoted_identifier], [is_schema_bound], [uses_database_collation], [is_recompiled], [null_on_null_input], [execute_as_principal_id] FROM sys.sql_modules
Sql 2008
SELECT [object_id], [definition], [uses_ansi_nulls], [uses_quoted_identifier], [is_schema_bound], [uses_database_collation], [is_recompiled], [null_on_null_input], [execute_as_principal_id] FROM sys.sql_modules
Sql 2008 R2
SELECT [object_id], [definition], [uses_ansi_nulls], [uses_quoted_identifier], [is_schema_bound], [uses_database_collation], [is_recompiled], [null_on_null_input], [execute_as_principal_id] FROM sys.sql_modules
Sql 2012
SELECT [object_id], [definition], [uses_ansi_nulls], [uses_quoted_identifier], [is_schema_bound], [uses_database_collation], [is_recompiled], [null_on_null_input], [execute_as_principal_id], [is_contained] FROM sys.sql_modules
Back to Top
sys.stats
statistics object that exists for the tables, indexes, and indexed views in the database. Note Every index will have a corresponding statistics row with the same name and ID (index_id = stats_id), but not every statistics row has a corresponding index. The catalog view, sys.stats_columns, provides statistics information for each column in the database. For more information, see sys.stats_columns (Transact-SQL)1 For more information about statistics, see Statistics2.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object to which these statistics belong. |
name | • | • | • | • | sysname | Name of the statistics. Is unique within the object. |
stats_id | • | • | • | • | int | ID of the statistics. Is unique within the object. |
auto_created | • | • | • | • | bit | Statistics were auto-created by the query processor. |
user_created | • | • | • | • | bit | Statistics were explicitly created by the user. |
no_recompute | • | • | • | • | bit | Statistics were created with the NORECOMPUTE option. |
has_filter | • | • | • | bit | 1 = Statistics have a filter and are computed only on rows that satisfy the filter definition. 0 = Statistics do not have a filter and are computed on all rows. |
|
filter_definition | • | • | • | nvarchar(max) | Expression for the subset of rows included in filtered statistics. NULL = Non-filtered statistics. |
|
is_temporary | • | bit | Indicate whether the statistics is temporary. Temporary statistics support AlwaysOn Availability Groups secondary databases that are enabled for read-only access. 0 = The statistics is not temporary. 1 = The statistics is temporary. Does not apply to SQL Azure. |
TSQL
Sql 2005SELECT [object_id], [name], [stats_id], [auto_created], [user_created], [no_recompute] FROM sys.stats
Sql 2008
SELECT [object_id], [name], [stats_id], [auto_created], [user_created], [no_recompute], [has_filter], [filter_definition] FROM sys.stats
Sql 2008 R2
SELECT [object_id], [name], [stats_id], [auto_created], [user_created], [no_recompute], [has_filter], [filter_definition] FROM sys.stats
Sql 2012
SELECT [object_id], [name], [stats_id], [auto_created], [user_created], [no_recompute], [has_filter], [filter_definition], [is_temporary] FROM sys.stats
Back to Top
sys.stats_columns
column that is part of sys.stats statistics.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object of which this column is part. |
stats_id | • | • | • | • | int | ID of the statistics of which this column is part. |
stats_column_id | • | • | • | • | int | 1-based ordinal within set of stats columns. |
column_id | • | • | • | • | int | ID of the column from sys.columns |
TSQL
Sql 2005SELECT [object_id], [stats_id], [stats_column_id], [column_id] FROM sys.stats_columns
Sql 2008
SELECT [object_id], [stats_id], [stats_column_id], [column_id] FROM sys.stats_columns
Sql 2008 R2
SELECT [object_id], [stats_id], [stats_column_id], [column_id] FROM sys.stats_columns
Sql 2012
SELECT [object_id], [stats_id], [stats_column_id], [column_id] FROM sys.stats_columns
Back to Top
sys.synonyms
synonym object that is sys.objects.type = SN.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | • | sysname | sys.objects | Object name. |
object_id | • | • | • | • | int | sys.objects | Object identification number. Is unique within a database. |
principal_id | • | • | • | • | int | sys.objects | 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 | sys.objects | 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 | sys.objects | ID of the object to which this object belongs. 0 = Not a child object. |
type | • | • | • | • | char(2) | sys.objects | 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) | sys.objects | 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 | sys.objects | Date the object was created. |
modify_date | • | • | • | • | datetime | sys.objects | 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 | sys.objects | Object is created by an internal SQL Server component. |
is_published | • | • | • | • | bit | sys.objects | Object is published. |
is_schema_published | • | • | • | • | bit | sys.objects | Only the schema of the object is published. |
base_object_name | • | • | • | • | nvarchar(1035) | Fully quoted name of the object to which the user of this synonym is redirected. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [base_object_name] FROM sys.synonyms
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [base_object_name] FROM sys.synonyms
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [base_object_name] FROM sys.synonyms
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [base_object_name] FROM sys.synonyms
Back to Top
sys.system_columns
column of system objects that have columns.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object to which this column belongs. |
name | • | • | • | • | sysname | Name of the column. Is unique within the object. |
column_id | • | • | • | • | int | ID of the column. Is unique within the object. Column IDs might not be sequential. |
system_type_id | • | • | • | • | tinyint | ID of the system-type of the column |
user_type_id | • | • | • | • | int | ID of the type of the column as defined by the user. To return the name of the type, join to the sys.types1 catalog view on this column. |
max_length | • | • | • | • | smallint | Maximum length (in bytes) of column. -1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml. For text columns, the max_length value will be 16 or the value set by sp_tableoption 'text in row'. |
precision | • | • | • | • | tinyint | Precision of the column if numeric-based; otherwise, 0. |
scale | • | • | • | • | tinyint | Scale of the column if numeric-based; otherwise, 0. |
collation_name | • | • | • | • | sysname | Name of the collation of the column if character-based; otherwise, NULL. |
is_nullable | • | • | • | • | bit | 1 = Column is nullable. |
is_ansi_padded | • | • | • | • | bit | 1 = Column uses ANSI_PADDING ON behavior if character, binary, or variant. 0 = Column is not character, binary, or variant. |
is_rowguidcol | • | • | • | • | bit | 1 = Column is a declared ROWGUIDCOL. |
is_identity | • | • | • | • | bit | 1 = Column has identity values. |
is_computed | • | • | • | • | bit | 1 = Column is a computed column. |
is_filestream | • | • | • | • | bit | 1 = Column is declared to use filestream storage. |
is_replicated | • | • | • | • | bit | 1 = Column is replicated. |
is_non_sql_subscribed | • | • | • | • | bit | 1 = Column has a non-SQL Server subscriber. |
is_merge_published | • | • | • | • | bit | 1 = Column is merge-published. |
is_dts_replicated | • | • | • | • | bit | 1 = Column is replicated by using SQL Server 2005 Integration Services (SSIS). |
is_xml_document | • | • | • | • | bit | 1 = Content is a complete XML document. 0 = Content is a document fragment, or the column data type is not xml. |
xml_collection_id | • | • | • | • | int | Non-zero if the column data type is xml and the XML is typed. The value will be the ID of the collection containing the validating XML schema namespace of the column. 0 = No XML schema collection. |
default_object_id | • | • | • | • | int | ID of the default object, regardless of whether it is a stand-alone sys.sp_bindefault2, or an inline, column-level DEFAULT constraint. The parent_object_id column of an inline column-level default object is a reference back to the table itself. Or, 0 if there is no default. |
rule_object_id | • | • | • | • | int | ID of the stand-alone rule bound to the column by using sys.sp_bindrule. 0 = No stand-alone rule. For column-level CHECK constraints, see sys.check_constraints (Transact-SQL)3. |
is_sparse | • | • | • | bit | 1 = Column is a sparse column. For more information, see Using Sparse Columns4. | |
is_column_set | • | • | • | bit | 1 = Column is a column set. For more information, see Using Column Sets5. |
TSQL
Sql 2005SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id] FROM sys.system_columns
Sql 2008
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set] FROM sys.system_columns
Sql 2008 R2
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set] FROM sys.system_columns
Sql 2012
SELECT [object_id], [name], [column_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_ansi_padded], [is_rowguidcol], [is_identity], [is_computed], [is_filestream], [is_replicated], [is_non_sql_subscribed], [is_merge_published], [is_dts_replicated], [is_xml_document], [xml_collection_id], [default_object_id], [rule_object_id], [is_sparse], [is_column_set] FROM sys.system_columns
Back to Top
sys.system_functions
Contains one row for all schema-scoped system objects that are included with Microsoft SQL Server. All system objects are contained in the schemas named sys or INFORMATION_SCHEMA.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | 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 other 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 the object is contained in. For all schema-scoped system objects that included with SQL Server, 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 Microsoft SQL Server component. |
is_published | • | • | • | • | bit | Object is published. |
is_schema_published | • | • | • | • | bit | Only the schema of the object is published. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.system_functions
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.system_functions
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.system_functions
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.system_functions
Back to Top
sys.system_objects
Contains one row for all schema-scoped system objects that are included with Microsoft SQL Server. All system objects are contained in the schemas named sys or INFORMATION_SCHEMA.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | 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 other 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 the object is contained in. For all schema-scoped system objects that included with SQL Server, 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 Microsoft SQL Server component. |
is_published | • | • | • | • | bit | Object is published. |
is_schema_published | • | • | • | • | bit | Only the schema of the object is published. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.system_objects
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.system_objects
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.system_objects
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.system_objects
Back to Top
sys.system_parameters
Contains one row for each system object that has parameters.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
object_id | • | • | • | • | int | ID of the object to which this parameter belongs. |
name | • | • | • | • | sysname | Name of the parameter. Is unique within the object. If the object is a scalar function, the parameter name is an empty string in the row representing the return value. |
parameter_id | • | • | • | • | int | ID of the parameter. Is unique within the object. If the object is a scalar function, parameter_id = 0 represents the return value. |
system_type_id | • | • | • | • | tinyint | ID of the system type of the parameter. |
user_type_id | • | • | • | • | int | ID of the type of the parameter as defined by the user. To return the name of the type, join to the sys.types1 catalog view on this column. |
max_length | • | • | • | • | smallint | Maximum length of the parameter, in bytes. Value will be -1 for when column data type is varchar(max), nvarchar(max), varbinary(max), or xml. |
precision | • | • | • | • | tinyint | Precision of the parameter if numeric-based; otherwise, 0. |
scale | • | • | • | • | tinyint | Scale of the parameter if numeric-based; otherwise, 0. |
is_output | • | • | • | • | bit | 1 = Parameter is output (or return); otherwise, 0. |
is_cursor_ref | • | • | • | • | bit | 1 = Parameter is a cursor-reference parameter. |
has_default_value | • | • | • | • | bit | 1 = Parameter has default value. SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column will always have a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules2 catalog view, or use the OBJECT_DEFINITION3 system function. |
is_xml_document | • | • | • | • | bit | 1 = Content is a complete XML document. 0 = Content is a document fragment or the data type of the column is not xml. |
default_value | • | • | • | • | sql_variant | If has_default_value is 1, the value of this column is the value of the default for the parameter; otherwise NULL. |
xml_collection_id | • | • | • | • | int | Non-zero if the data type of the parameter is xml and the XML is typed. The value is the ID of the collection that contains the validating XML schema namespace for the parameter. 0 = There is no XML schema collection. |
TSQL
Sql 2005SELECT [object_id], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref], [has_default_value], [is_xml_document], [default_value], [xml_collection_id] FROM sys.system_parameters
Sql 2008
SELECT [object_id], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref], [has_default_value], [is_xml_document], [default_value], [xml_collection_id] FROM sys.system_parameters
Sql 2008 R2
SELECT [object_id], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref], [has_default_value], [is_xml_document], [default_value], [xml_collection_id] FROM sys.system_parameters
Sql 2012
SELECT [object_id], [name], [parameter_id], [system_type_id], [user_type_id], [max_length], [precision], [scale], [is_output], [is_cursor_ref], [has_default_value], [is_xml_document], [default_value], [xml_collection_id] FROM sys.system_parameters
Back to Top
sys.system_stored_procedures
Contains one row for all schema-scoped system objects that are included with Microsoft SQL Server. All system objects are contained in the schemas named sys or INFORMATION_SCHEMA.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | 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 other 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 the object is contained in. For all schema-scoped system objects that included with SQL Server, 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 Microsoft SQL Server component. |
is_published | • | • | • | • | bit | Object is published. |
is_schema_published | • | • | • | • | bit | Only the schema of the object is published. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.system_stored_procedures
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.system_stored_procedures
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.system_stored_procedures
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published] FROM sys.system_stored_procedures
Back to Top
sys.system_views
Contains one row for each system view that is shipped with SQL Server 2012. All system views are contained in the schemas named sys or INFORMATION_SCHEMA.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | • | sysname | sys.objects | Object name. |
object_id | • | • | • | • | int | sys.objects | Object identification number. Is unique within a database. |
principal_id | • | • | • | • | int | sys.objects | 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 | sys.objects | 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 | sys.objects | ID of the object to which this object belongs. 0 = Not a child object. |
type | • | • | • | • | char(2) | sys.objects | 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) | sys.objects | 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 | sys.objects | Date the object was created. |
modify_date | • | • | • | • | datetime | sys.objects | 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 | sys.objects | Object is created by an internal SQL Server component. |
is_published | • | • | • | • | bit | sys.objects | Object is published. |
is_schema_published | • | • | • | • | bit | sys.objects | Only the schema of the object is published. |
is_replicated | • | • | • | • | bit | 1 = View is replicated. | |
has_replication_filter | • | • | • | • | bit | 1 = View has a replication filter. | |
has_opaque_metadata | • | • | • | • | bit | 1 = VIEW_METADATA option specified for view. For more information, see CREATE VIEW (Transact-SQL)2. | |
has_unchecked_assembly_data | • | • | • | • | bit | 1 = Table contains persisted data that depends on an assembly whose definition changed during the last ALTER ASSEMBLY. Will be reset to 0 after the next successful DBCC CHECKDB or DBCC CHECKTABLE. | |
with_check_option | • | • | • | • | bit | 1 = WITH CHECK OPTION was specified in the view definition. | |
is_date_correlation_view | • | • | • | • | bit | 1 = View was created automatically by the system to store correlation information between datetime columns. Creation of this view was enabled by setting DATE_CORRELATION_OPTIMIZATION to ON. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_replicated], [has_replication_filter], [has_opaque_metadata], [has_unchecked_assembly_data], [with_check_option], [is_date_correlation_view] FROM sys.system_views
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_replicated], [has_replication_filter], [has_opaque_metadata], [has_unchecked_assembly_data], [with_check_option], [is_date_correlation_view] FROM sys.system_views
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_replicated], [has_replication_filter], [has_opaque_metadata], [has_unchecked_assembly_data], [with_check_option], [is_date_correlation_view] FROM sys.system_views
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_replicated], [has_replication_filter], [has_opaque_metadata], [has_unchecked_assembly_data], [with_check_option], [is_date_correlation_view] FROM sys.system_views
Back to Top
sys.table_types
Displays properties of user-defined table types. A table type is a type from which table variables or table-valued parameters could be declared. Each table type has a type_table_object_id that is a foreign key into the sys.objects1 catalog view. This id can be used for querying various catalog views, in a way similar to an object_id of a regular table, for discovering the structure of the table type such as its columns, constraints, etc.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | sysname | sys.types | Name of the type. Is unique within the schema. | |
system_type_id | • | • | • | tinyint | sys.types | ID of the internal system-type of the type. | |
user_type_id | • | • | • | int | sys.types | ID of the type. Is unique within the database. For system data types, user_type_id = system_type_id. | |
schema_id | • | • | • | int | sys.types | ID of the schema to which the type belongs. | |
principal_id | • | • | • | int | sys.types | ID of the individual owner if different from 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 is no alternate individual owner. |
|
max_length | • | • | • | smallint | sys.types | Maximum length (in bytes) of the type. -1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml. For text columns, the max_length value will be 16. |
|
precision | • | • | • | tinyint | sys.types | Max precision of the type if it is numeric-based; otherwise, 0. | |
scale | • | • | • | tinyint | sys.types | Max scale of the type if it is numeric-based; otherwise, 0. | |
collation_name | • | • | • | sysname | sys.types | Name of the collation of the type if it is character-based; other wise, NULL. | |
is_nullable | • | • | • | bit | sys.types | Type is nullable. | |
is_user_defined | • | • | • | bit | sys.types | 1 = User-defined type. 0 = SQL Server system data type. |
|
is_assembly_type | • | • | • | bit | sys.types | 1 = Implementation of the type is defined in a CLR assembly. 0 = Type is based on a SQL Server system data type. |
|
default_object_id | • | • | • | int | sys.types | ID of the stand-alone default that is bound to the type by using sp_bindefault1. 0 = No default exists. |
|
rule_object_id | • | • | • | int | sys.types | ID of the stand-alone rule that is bound to the type by using sp_bindrule2. 0 = No rule exists. |
|
is_table_type | • | • | • | bit | sys.types | Indicates the type is a table. | |
type_table_object_id | • | • | • | int | Object identification number. This number is unique within a database. |
TSQL
Sql 2008SELECT [name], [system_type_id], [user_type_id], [schema_id], [principal_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_user_defined], [is_assembly_type], [default_object_id], [rule_object_id], [is_table_type], [type_table_object_id] FROM sys.table_types
Sql 2008 R2
SELECT [name], [system_type_id], [user_type_id], [schema_id], [principal_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_user_defined], [is_assembly_type], [default_object_id], [rule_object_id], [is_table_type], [type_table_object_id] FROM sys.table_types
Sql 2012
SELECT [name], [system_type_id], [user_type_id], [schema_id], [principal_id], [max_length], [precision], [scale], [collation_name], [is_nullable], [is_user_defined], [is_assembly_type], [default_object_id], [rule_object_id], [is_table_type], [type_table_object_id] FROM sys.table_types
Back to Top
sys.tables
table object, currently only with sys.objects.type = U.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | • | sysname | sys.objects | Object name. |
object_id | • | • | • | • | int | sys.objects | Object identification number. Is unique within a database. |
principal_id | • | • | • | • | int | sys.objects | 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 | sys.objects | 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 | sys.objects | ID of the object to which this object belongs. 0 = Not a child object. |
type | • | • | • | • | char(2) | sys.objects | 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) | sys.objects | 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 | sys.objects | Date the object was created. |
modify_date | • | • | • | • | datetime | sys.objects | 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 | sys.objects | Object is created by an internal SQL Server component. |
is_published | • | • | • | • | bit | sys.objects | Object is published. |
is_schema_published | • | • | • | • | bit | sys.objects | Only the schema of the object is published. |
lob_data_space_id | • | • | • | • | int | A nonzero value is the ID of the data space (filegroup or partition scheme) that holds the text, ntext, and image data for this table. 0 = The table does not contain text, ntext, or image data. |
|
filestream_data_space_id | • | • | • | • | int | For internal system use only. | |
max_column_id_used | • | • | • | • | int | Maximum column ID ever used by this table. | |
lock_on_bulk_load | • | • | • | • | bit | Table is locked on bulk load. For more information, see sp_tableoption (Transact-SQL)2. | |
uses_ansi_nulls | • | • | • | • | bit | Table was created with the SET ANSI_NULLS database option ON. | |
is_replicated | • | • | • | • | bit | 1 = Table is published using snapshot replication or transactional replication. | |
has_replication_filter | • | • | • | • | bit | 1 = Table has a replication filter. | |
is_merge_published | • | • | • | • | bit | 1 = Table is published using merge replication. | |
is_sync_tran_subscribed | • | • | • | • | bit | 1 = Table is subscribed using an immediate updating subscription. | |
has_unchecked_assembly_data | • | • | • | • | bit | 1 = Table contains persisted data that depends on an assembly whose definition changed during the last ALTER ASSEMBLY. Will be reset to 0 after the next successful DBCC CHECKDB or DBCC CHECKTABLE. | |
text_in_row_limit | • | • | • | • | int | The maximum bytes allowed for text in row. 0 = Text in row option is not set. For more information, see sp_tableoption (Transact-SQL)2. |
|
large_value_types_out_of_row | • | • | • | • | bit | 1 = Large value types are stored out-of-row. For more information, see sp_tableoption (Transact-SQL)2. | |
is_tracked_by_cdc | • | • | • | bit | 1 = Table is enabled for change data capture. For more information, see sys.sp_cdc_enable_table (Transact-SQL)3. | ||
lock_escalation | • | • | • | tinyint | The value of the LOCK_ESCALATION option for the table: 0 = TABLE 1 = DISABLE 2 = AUTO |
||
lock_escalation_desc | • | • | • | nvarchar(60) | A text description of the lock_escalation option for the table. Possible values are: TABLE, DISABLE, and AUTO. | ||
is_filetable | • | bit | 1 = Table is a FileTable. For more information about FileTables, see FileTables (SQL Server)4. Does not apply to SQL Azure. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [lob_data_space_id], [filestream_data_space_id], [max_column_id_used], [lock_on_bulk_load], [uses_ansi_nulls], [is_replicated], [has_replication_filter], [is_merge_published], [is_sync_tran_subscribed], [has_unchecked_assembly_data], [text_in_row_limit], [large_value_types_out_of_row] FROM sys.tables
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [lob_data_space_id], [filestream_data_space_id], [max_column_id_used], [lock_on_bulk_load], [uses_ansi_nulls], [is_replicated], [has_replication_filter], [is_merge_published], [is_sync_tran_subscribed], [has_unchecked_assembly_data], [text_in_row_limit], [large_value_types_out_of_row], [is_tracked_by_cdc], [lock_escalation], [lock_escalation_desc] FROM sys.tables
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [lob_data_space_id], [filestream_data_space_id], [max_column_id_used], [lock_on_bulk_load], [uses_ansi_nulls], [is_replicated], [has_replication_filter], [is_merge_published], [is_sync_tran_subscribed], [has_unchecked_assembly_data], [text_in_row_limit], [large_value_types_out_of_row], [is_tracked_by_cdc], [lock_escalation], [lock_escalation_desc] FROM sys.tables
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [lob_data_space_id], [filestream_data_space_id], [max_column_id_used], [lock_on_bulk_load], [uses_ansi_nulls], [is_replicated], [has_replication_filter], [is_merge_published], [is_sync_tran_subscribed], [has_unchecked_assembly_data], [text_in_row_limit], [large_value_types_out_of_row], [is_tracked_by_cdc], [lock_escalation], [lock_escalation_desc], [is_filetable] FROM sys.tables
Back to Top
sys.trigger_event_types
event or event group on which a trigger can fire.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
type | • | • | • | int | Type of event or event group that causes a trigger to fire. | |
type_name | • | • | • | nvarchar(128) | Name of an event or event group. This can be specified in the FOR clause of a CREATE TRIGGER1 statement. | |
parent_type | • | • | • | int | Type of event group that is the parent of the event or event group. |
TSQL
Sql 2008SELECT [type], [type_name], [parent_type] FROM sys.trigger_event_types
Sql 2008 R2
SELECT [type], [type_name], [parent_type] FROM sys.trigger_event_types
Sql 2012
SELECT [type], [type_name], [parent_type] FROM sys.trigger_event_types
Back to Top
sys.trigger_events
Contains a row per event for which a trigger fires. Note sys.trigger_events does not apply to event notifications.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
object_id | • | • | • | • | int | sys.events | ID of the trigger or event notification. This value, together with type, uniquely identifies the row. |
type | • | • | • | • | int | sys.events | Type of event that causes the trigger to fire. For a list of events, see the following table. |
type_desc | • | • | • | • | nvarchar(60) | sys.events | Description of the type of event that causes a trigger to fire. For a list of events, see the following table. |
is_trigger_event | • | • | • | • | bit | sys.events | 1 = Trigger event. 0 = Notification event. |
is_first | • | • | • | • | bit | Trigger is marked to be the first to fire for this event. | |
is_last | • | • | • | • | bit | Trigger is marked to be the last to fire for this event. | |
event_group_type | • | • | • | int | sys.events | Event group on which the trigger or event notification is created, or null if not created on an event group. | |
event_group_type_desc | • | • | • | nvarchar(60) | sys.events | Description of the event group on which the trigger or event notification is created, or null if not created on an event group. |
TSQL
Sql 2005SELECT [object_id], [type], [type_desc], [is_trigger_event], [is_first], [is_last] FROM sys.trigger_events
Sql 2008
SELECT [object_id], [type], [type_desc], [is_trigger_event], [event_group_type], [event_group_type_desc], [is_first], [is_last] FROM sys.trigger_events
Sql 2008 R2
SELECT [object_id], [type], [type_desc], [is_trigger_event], [event_group_type], [event_group_type_desc], [is_first], [is_last] FROM sys.trigger_events
Sql 2012
SELECT [object_id], [type], [type_desc], [is_trigger_event], [event_group_type], [event_group_type_desc], [is_first], [is_last] FROM sys.trigger_events
Back to Top
sys.triggers
object that is a trigger, with a type of TR or TA. DML trigger names are schema-scoped and, therefore, are visible in sys.objects. DDL trigger names are scoped by the parent entity and are only visible in this view. The parent_class and name columns uniquely identify the trigger in the database.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
name | • | • | • | • | sysname | Trigger name. DML trigger names are schema-scoped. DDL trigger names are scoped with respect to the parent entity. |
object_id | • | • | • | • | int | Object identification number. Is unique within a database. |
parent_class | • | • | • | • | tinyint | Class of the parent of the trigger. 0 = Database, for the DDL triggers. 1 = Object or column for the DML triggers. |
parent_class_desc | • | • | • | • | nvarchar(60) | Description of the parent class of the trigger. DATABASE OBJECT_OR_COLUMN |
parent_id | • | • | • | • | int | ID of the parent of the trigger, as follows: 0 = Triggers that are database-parented triggers. For DML triggers, this is the object_id of the table or view on which the DML trigger is defined. |
type | • | • | • | • | char(2) | Object type: TA = Assembly (CLR) trigger TR = SQL trigger |
type_desc | • | • | • | • | nvarchar(60) | Description of object type. CLR_TRIGGER SQL_TRIGGER |
create_date | • | • | • | • | datetime | Date the trigger was created. |
modify_date | • | • | • | • | datetime | Date the object was last modified by using an ALTER statement. |
is_ms_shipped | • | • | • | • | bit | Trigger created on behalf of the user by an internal SQL Server 2005 component. |
is_disabled | • | • | • | • | bit | Trigger is disabled. |
is_not_for_replication | • | • | • | • | bit | Trigger was created as NOT FOR REPLICATION. |
is_instead_of_trigger | • | • | • | • | bit | 1 = INSTEAD OF triggers 0 = AFTER triggers. |
TSQL
Sql 2005SELECT [name], [object_id], [parent_class], [parent_class_desc], [parent_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_disabled], [is_not_for_replication], [is_instead_of_trigger] FROM sys.triggers
Sql 2008
SELECT [name], [object_id], [parent_class], [parent_class_desc], [parent_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_disabled], [is_not_for_replication], [is_instead_of_trigger] FROM sys.triggers
Sql 2008 R2
SELECT [name], [object_id], [parent_class], [parent_class_desc], [parent_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_disabled], [is_not_for_replication], [is_instead_of_trigger] FROM sys.triggers
Sql 2012
SELECT [name], [object_id], [parent_class], [parent_class_desc], [parent_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_disabled], [is_not_for_replication], [is_instead_of_trigger] FROM sys.triggers
Back to Top
sys.views
view object, with sys.objects.type = V.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Inherited From | Description |
---|---|---|---|---|---|---|---|
name | • | • | • | • | sysname | sys.objects | Object name. |
object_id | • | • | • | • | int | sys.objects | Object identification number. Is unique within a database. |
principal_id | • | • | • | • | int | sys.objects | 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 | sys.objects | 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 | sys.objects | ID of the object to which this object belongs. 0 = Not a child object. |
type | • | • | • | • | char(2) | sys.objects | 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) | sys.objects | 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 | sys.objects | Date the object was created. |
modify_date | • | • | • | • | datetime | sys.objects | 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 | sys.objects | Object is created by an internal SQL Server component. |
is_published | • | • | • | • | bit | sys.objects | Object is published. |
is_schema_published | • | • | • | • | bit | sys.objects | Only the schema of the object is published. |
is_replicated | • | • | • | • | bit | 1 = View is replicated. | |
has_replication_filter | • | • | • | • | bit | 1 = View has a replication filter. | |
has_opaque_metadata | • | • | • | • | bit | 1 = VIEW_METADATA option specified for view. For more information, see CREATE VIEW (Transact-SQL)2. | |
has_unchecked_assembly_data | • | • | • | • | bit | 1 = View contains persisted data that depends on an assembly whose definition changed during the last ALTER ASSEMBLY. Resets to 0 after the next successful DBCC CHECKDB or DBCC CHECKTABLE. | |
with_check_option | • | • | • | • | bit | 1 = WITH CHECK OPTION was specified in the view definition. | |
is_date_correlation_view | • | • | • | • | bit | 1 = View was created automatically by the system to store correlation information between datetime columns. Creation of this view was enabled by setting DATE_CORRELATION_OPTIMIZATION to ON. |
TSQL
Sql 2005SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_replicated], [has_replication_filter], [has_opaque_metadata], [has_unchecked_assembly_data], [with_check_option], [is_date_correlation_view] FROM sys.views
Sql 2008
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_replicated], [has_replication_filter], [has_opaque_metadata], [has_unchecked_assembly_data], [with_check_option], [is_date_correlation_view] FROM sys.views
Sql 2008 R2
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_replicated], [has_replication_filter], [has_opaque_metadata], [has_unchecked_assembly_data], [with_check_option], [is_date_correlation_view] FROM sys.views
Sql 2012
SELECT [name], [object_id], [principal_id], [schema_id], [parent_object_id], [type], [type_desc], [create_date], [modify_date], [is_ms_shipped], [is_published], [is_schema_published], [is_replicated], [has_replication_filter], [has_opaque_metadata], [has_unchecked_assembly_data], [with_check_option], [is_date_correlation_view] FROM sys.views
Back to Top
No comments:
Post a Comment