December 6, 2012

Object Catalog Views

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

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

No comments:

Post a Comment

Total Pageviews