Object Catalog Views

Se more view version maps here: Microsoft Sql System View Version Maps

• 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 2005
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] 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 2005
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
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 2005
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
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 2005
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
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 2005
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
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 2005
SELECT [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 2005
SELECT [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 2005
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
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 2005
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] 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 2005
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], [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 2005
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
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 2005
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
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 2005
SELECT [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 2005
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
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 2005
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
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 2005
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
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 2008
SELECT [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 2005
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
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 2005
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], [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 2005
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
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 2005
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] 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 . However, you can query catalog views to see the metadata of internal tables.

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 2005
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
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 2005
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
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 2005
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
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 2005
SELECT [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 2005
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
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 2005
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.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 2005
SELECT [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 2005
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
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 2005
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
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 2005
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
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 2008
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 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 2005
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
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 2005
SELECT [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 2005
SELECT [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 2005
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
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 2005
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] 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 2005
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
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 2005
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
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 2005
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
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 2005
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
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 2005
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
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 2008
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 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 2005
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] 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 2008
SELECT [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 2005
SELECT [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 2005
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
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 2005
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
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

---