This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects. You can find a list of all of the deprecated views in the introduction post.
The compatibility view sysconstraints returns rows for all of the constraints in a database. These include primary and unique keys, foreign keys, check, and default constraints. For each of these types of constraints the compatibility view provides a starting point for understanding the constraints on the database.
The replacements catalog views for sysconstraints is broken out across four different catalog views. One that represents each of the four types of constraints in the SQL Server database. The catalog views are sys.key_constraints, sys.foreign_keys, sys.check_constraints, and sys.default_constraints. The main benefit that you will see in separating the types of constraints is the increase in the amount of metadata included in each catalog view, to better describe the constraint.
Status Column
As with other compatibility views, sysconstraints includes a status column. The values in the column can be divided into two groups. The first identifies the type of constraint in the row. And the second identifies if the constraint is at the table or column level.
With the constraint type values, the bit values are overloaded. Since a foreign key is represented by 0×3, then it will also match to the 0×1 and 0×2 bits. Because of this, the bits are mutually exclusive and the highest value in the range represents the type of constraint. The bits that describe the type of constraint are:
- 0×1: Indicates PRIMARY KEY constraint
- 0×2: Indicates UNIQUE KEY constraint.
- 0×3: Indicates FOREIGN KEY constraint.
- 0×4: Indicates CHECK constraint.
- 0×5: Indicates DEFAULT constraint.
The second set of values are those that identify whether the constraint is at the table or column level. At any one time, the constraint will only have one or the other value. These bit values are:
- 0×16: Indicates column-level constraint.
- 0×32: Indicates table-level constraint.
Base Compatibility Columns
Besides the status column, there are six other columns in sysconstraints. The columns can be divided into two sets. The first set, which includes constid, id, and colid, describes the object tied to the constraint, the object being constrained, and the column related to the constraint, respectively. These help provide the base for retrieving additional information regarding what objects the constraint affects. The next set, which includes spare1, actions, and error, are internal columns that don’t provide any value when investigating constraints.
Probably the best way to look at sysconstraints and see it’s failings is to instead look at the replacement catalog views and the information that they provide. In the next few sections, we’ll walk through doing that.
Primary Key and Unique Constraints
The first catalog view to look at is sys.key_constraints, this view returns one row for primary key and unique constraint in the database. The view uses all of the columns from sys.objects as a base, along with columns for index associated with the constraint and whether the name was for the constraint was generated by the system.
Querying sys.key_constraints
There are a few columns from sys.key_constraints that are typically of concern when examining primary key and unique constraints. These columns, included in Listing 1, cover the object id, it’s parent object id, the associated index, and the type description. With this information, the additional information on the constraint can be found through sys.indexes and sys.index_columns, which is covered later in this series. For this discussion, we’ll keep the query simple and return just the index id.
Listing 1 – Sys.key_constraints query for Primary Key and Unique Constraints SELECT name ,object_id ,parent_object_id ,type_desc ,create_date ,modify_date ,unique_index_id ,is_system_named FROM sys.key_constraints
Querying sysconstraints
Retrieving the same information as sys.key_constraints through sysconstraints is less straight forward. Since the compatibility view contains four sets of information, the first task is to limit the results. This could be performed by either bit comparisons or through the use of the OBJECTPROPERTY function, both methods are shown in the query in listing 2. Next, the a join is needed to sysobjects to obtain the name of the constraint and the create and modify dates. Finally, a join to sysindexes is included, to retrieve the index id by joining on the object id and the name of the index. Without a comparison of the names between the two views, it is not possible to retrieve the index id of the constraint.
Listing 2 – Sysconstraints query for Primary Key and Unique Constraints SELECT s.name ,c.constid ,c.id ,CASE WHEN CONVERT(INT,c.status & 0x5) = 5 THEN 'DEFAULT_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x4) = 4 THEN 'CHECK_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x3) = 3 THEN 'FOREIGN_KEY_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x2) = 2 THEN 'UNIQUE_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x1) = 1 THEN 'PRIMARY_KEY_CONSTRAINT' END AS type_desc ,s.crdate ,s.refdate ,i.indid ,CONVERT(INT,c.status & 0x10) / 16 AS is_column_level_constraint ,CONVERT(INT,c.status & 0x20) / 32 AS is_table_level_constraint ,c.status FROM sysconstraints c INNER JOIN sysobjects s ON c.constid = s.id INNER JOIN sysindexes i ON c.id = i.id AND s.name = i.name WHERE OBJECTPROPERTY(c.constid,'IsPrimaryKey') = 1 OR OBJECTPROPERTY(c.constid,'IsUniqueCnst') = 1
Foreign Key Constraints
The next catalog view to look at is sys.foreign_keys, this view returns one row for foreign key constraint in the database. The view uses all of the columns from sys.objects as a base, along with information relating to the structure and behavior of the foreign key relationship. This includes information such as how to behave during a delete or update operation, whether the foreign key is trusted, and if the constraint is enabled or disabled.
Querying sys.key_constraints
Foreign keys are slightly more complex that primary key and unique constraints, because of this there are more columns included in a basic foreign key query against the catalog views. Some of the columns included are the parent_object_id and referenced_object_id. Then the columns is_disabled and is_not_trusted to determine if the foreign key is enabled or trusted. Finally the actions to execute in the event that data is changed within the foreign key relationship with the delete_referential_action_desc and update_referential_action_desc columns. The query that includes all of this is in listing 3.
Listing 3 – Sys.foreign_keys query for Foreign Key Constraints SELECT name ,object_id ,parent_object_id ,type_desc ,create_date ,modify_date ,referenced_object_id ,is_disabled ,is_not_trusted ,delete_referential_action_desc ,update_referential_action_desc ,is_system_named FROM sys.foreign_keys
Querying sysconstraints
As with the previous query against sysconstraints, there are more steps that just querying a single catalog view required in order to get the similar results provided by the sys.foreign_keys catalog view. For starters, the non-foreign key rows need to be removed from the results through either the OBJECTPROPERTY function or the status column. Next The trusted, disabled, and whether deletes and updates cause cascading is retrieved through OBJECTPROPERTY. It is important to note that the foreign key delete and update actions only return whether the operation should cascade. The set NULL and set default options do not appear to be readily available within the compatibility view or other properties. The last piece of data needed for the query is a summary of sysforeignkeys in order to retrieve the parent and referenced table information. A query for this activity is included in listing 4.
Listing 4 – Sysconstraints query for Foreign Key Constraints SELECT s.name ,c.constid ,c.id ,f.fkeyid ,CASE WHEN CONVERT(INT,c.status & 0x5) = 5 THEN 'DEFAULT_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x4) = 4 THEN 'CHECK_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x3) = 3 THEN 'FOREIGN_KEY_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x2) = 2 THEN 'UNIQUE_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x1) = 1 THEN 'PRIMARY_KEY_CONSTRAINT' END AS type_desc ,s.crdate ,s.refdate ,f.rkeyid ,CONVERT(INT,c.status & 0x10) / 16 AS is_column_level_constraint ,CONVERT(INT,c.status & 0x20) / 32 AS is_table_level_constraint ,c.status ,OBJECTPROPERTY(c.constid, 'CnstIsDisabled') AS is_disabled ,OBJECTPROPERTY(c.constid, 'CnstIsNotTrusted') AS is_not_trusted ,OBJECTPROPERTY(c.constid, 'CnstIsDeleteCascade') AS cascade_delete_action ,OBJECTPROPERTY(c.constid, 'CnstIsUpdateCascade') AS cascade_update_action FROM sysconstraints c INNER JOIN sysobjects s ON c.constid = s.id INNER JOIN (SELECT constid, fkeyid, rkeyid FROM sysforeignkeys GROUP BY constid, fkeyid, rkeyid) f ON c.constid = f.constid WHERE OBJECTPROPERTY(c.constid,'IsForeignKey') = 1
Check Constraints
The third catalog view to look at is sys.check_constraints, this view returns one row for check constraint in the database. This view also uses all of the columns from sys.objects as a base, along with information on the column in which the check constraint exists and the SQL expression for the constraint. Similar to foreign keys, there are also columns identifying whether the constraint is disable or trusted.
Querying sys.check_constraints
The query against sys.check_constraints is similar to the previous catalog view queries in this post. The chief difference is the inclusion of the parent_column_id, to identify the column with the check constraint, and the definition, to provide the SQL expression for the check constraint. These columns and the other necessary columns are included in the query in listing 5.
Listing 5 – Sys.check_constraints query for Check Constraints SELECT name ,object_id ,parent_object_id ,parent_column_id ,type_desc ,create_date ,modify_date ,is_disabled ,is_not_trusted ,definition ,is_system_named FROM sys.check_constraints
Querying sysconstraints
As before, getting all of the information to define the check constraint requires accessing multiple sources when using sysconstraints. The first step again is to retrieve only those rows that are check constraints, through either the status column or the OBJECTPROPERTY function. Joining to sysobjects provides the name for the check constraint along with the create and modify dates. The OBJECTPROPERTY function is also used to identify if the check constraint is enable and trusted. Lastly, a join to syscomments provides access to the text column which contains the SQL expression definition. The query for these steps is provided in listing 6.
Listing 6 – Sysconstraints query for Check Constraints SELECT s.name ,c.constid ,c.id ,c.colid ,CASE WHEN CONVERT(INT,c.status & 0x5) = 5 THEN 'DEFAULT_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x4) = 4 THEN 'CHECK_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x3) = 3 THEN 'FOREIGN_KEY_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x2) = 2 THEN 'UNIQUE_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x1) = 1 THEN 'PRIMARY_KEY_CONSTRAINT' END AS type_desc ,s.crdate ,s.refdate ,CONVERT(INT,c.status & 0x10) / 16 AS is_column_level_constraint ,CONVERT(INT,c.status & 0x20) / 32 AS is_table_level_constraint ,c.status ,OBJECTPROPERTY(c.constid, 'CnstIsDisabled') AS is_disabled ,OBJECTPROPERTY(c.constid, 'CnstIsNotTrusted') AS is_not_trusted ,t.text FROM sysconstraints c INNER JOIN sysobjects s ON c.constid = s.id INNER JOIN syscomments t ON c.constid = t.id WHERE OBJECTPROPERTY(c.constid,'IsCheckCnst') = 1
Default Constraints
The last catalog view to look at is sys.default_constraints, this view returns one row for default constraint in the database. Like all of the other catalog views in this post, it uses all of the columns from sys.objects as a base. There are additional columns in the view that identify the column for the default constraint and the SQL expression for the default value.
Querying sys.default_constraints
There are not many differences between the query for sys.default_constraints than that of sys.check_constraints. There are the parent_column_id and definition columns; which map the default constraint to the specified column and provide the expression used for the default value. The real difference between the two lack of the check for being enabled and trusted, since default constraints are always enabled and have no trust options. The query provided in listing 7 represents a typical query against sys.default_constraints.
Listing 7 – Sys.default_constraints query for Default Constraints SELECT name ,object_id ,parent_object_id ,parent_column_id ,type_desc ,create_date ,modify_date ,definition ,is_system_named FROM sys.default_constraints
Querying sysconstraints
As before, getting all of the information to define the check constraint requires accessing multiple sources when using sysconstraints. The first step again is to retrieve only those rows that are check constraints, through either the status column or the OBJECTPROPERTY function. Joining to sysobjects provides the name for the check constraint along with the create and modify dates. Lastly, a join to syscomments provides access to the text column which contains the SQL expression definition. The query for these steps is provided in listing 6.
For the final sysconstraints query, the data required to obtain the default constraint information will also require multiple sources. The results need to be trimmed to only include default constraints through either the OBJECTPROPERTY function or status column. Next a join to sysobjects brings in the name of the constraint and the create and modify dates. The last information is from syscomments, which provides the text column containing the SQL expression for the default value. The query for these steps is provided in listing 8.
Listing 8 – Sysconstraints query for Default Constraints SELECT s.name ,c.constid ,c.id ,c.colid ,CASE WHEN CONVERT(INT,c.status & 0x5) = 5 THEN 'DEFAULT_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x4) = 4 THEN 'CHECK_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x3) = 3 THEN 'FOREIGN_KEY_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x2) = 2 THEN 'UNIQUE_CONSTRAINT' WHEN CONVERT(INT,c.status & 0x1) = 1 THEN 'PRIMARY_KEY_CONSTRAINT' END AS type_desc ,s.crdate ,s.refdate ,CONVERT(INT,c.status & 0x10) / 16 AS is_column_level_constraint ,CONVERT(INT,c.status & 0x20) / 32 AS is_table_level_constraint ,c.status ,t.text FROM sysconstraints c INNER JOIN sysobjects s ON c.constid = s.id INNER JOIN syscomments t ON c.constid = t.id WHERE OBJECTPROPERTY(c.constid,'IsDefaultCnst') = 1
Summary
In this post, we discussed the use of sysconstraints and how it has been replaced with catalog views sys.key_constraints, sys.foreign_keys, sys.check_constraints, and sys.default_constraints. One thing that should be apparent through each of compatibility view queries is they make getting easy and readily available information much more difficult. Instead of possibly losing data from moving off of these views, there is a wealth of information being lost by not migrating to the catalog views.
Do you see any reason to continue using sysconstraints? Is there anything missing from this post that people continuing to use the compatibility view should know?
Original article: Lost in Translation – Deprecated System Tables – sysconstraints
©2012 Strate SQL. All Rights Reserved.
Related posts:
- Lost in Translation – Deprecated System Tables – sysconfigures
- Lost in Translation – Deprecated System Tables – syscurconfigs
- Lost in Translation – Deprecated System Tables – sysaltfiles
