In this stairway, we would like to appeal to reasonably wide range of expertise and knowledge in SQL Server. I assume that if you have managed to get this far, you have a reasonable working knowledge of database objects and other types of metadata; in other words, you have a reasonable idea of what a view, table, function, procedure, schema, constraint and so on actually is.

In this level, I’ll introduce keys, constraints and relationships. As with the previous levels, I want to illustrate as much as possible with practical and immediately useful code. Some of the examples will seem simplistic to you but I hope to produce some information that will be new to even experienced DBAs or database developers: After all, some of the information surprised me.

Constraints

When you want to see what constraints are defined on a table, you can execute the old sp_helpconstraint stored procedure to do it. Here’s an example:

  EXEC sp_helpconstraint 'Sales.SpecialOfferProduct';

This procedure, as called, displays a number of different types of constraints, but if the result was more helpful, this would be a short stairway. Here is an example from AdventureWorks2012.

There are a number of system tables that can help us find out details about constraints such as (in the Information_Schema):

• Information_Schema.Check_Constraints
• Information_Schema.Constraint_Column_Usage
• Information_Schema.Constraint_Table_Usage
• Information_Schema.Referential_Constraints
• Information_Schema.Table_Constraints
• Information_Schema.Key_Column_Usage

As an alternative we have these system views:

• sys.check_constraints
• sys.default_constraints
• sys.key_constraints
• sys.foreign_key_columns
• sys.foreign_keys

We’ll be showing you how to use these views in this level.

In some ways, the Information_Schema views are better for summary information, except for the fact that column defaults aren’t considered actual constraints, and the Information_Schema views generally don’t perform as well as the catalog views.

You can, for example, find out the tables with the most constraints (not including default constraints) using this query:

SELECT table_schema + '.' + table_Name AS Table_Name, COUNT(*) AS Constraints
  FROM Information_Schema.CONSTRAINT_TABLE_USAGE
  GROUP BY table_schema + '.' + table_Name
  ORDER BY COUNT(*)DESC;

Here are my results from AdventureWorks2012 (I will use this database in all the examples in this level)

Or you can list out all the constraints other than default constraints, for a particular table using the following query.

SELECT constraint_name
  FROM Information_Schema.CONSTRAINT_TABLE_USAGE
  WHERE table_schema LIKE 'production' AND table_name LIKE 'product';

Here are my results:

Alternatively, you can use the system views to list all the constraints, as in the following query:

SELECT 
    OBJECT_SCHEMA_NAME(Parent_Object_ID) + '.'
       + OBJECT_NAME(Parent_Object_ID) AS TheTable,
    Name
  FROM sys.objects
  WHERE OBJECTPROPERTYEX(object_id, 'IsPrimaryKey') = 1
  ORDER BY TheTable;

Which produces:

If you need a list of constraints for just the table you specify (production.Product from AdventureWorks2012 in this example) then you can use this query:

SELECT 
    OBJECT_SCHEMA_NAME(Parent_Object_ID) + '.'
       + OBJECT_NAME(Parent_Object_ID) AS TheTable,
    name,
    LOWER(REPLACE(Type_Desc, '_', ' ')) as [Description]
  FROM sys.objects
  WHERE OBJECTPROPERTYEX(object_id, 'IsConstraint') = 1
    AND parent_object_ID = OBJECT_ID('Production.Product')
  ORDER BY type DESC;

Here are my results:

Naturally, if you just want to see all the primary keys, you’d just could use this query:

SELECT OBJECT_SCHEMA_NAME(Parent_Object_ID) + '.'
       + OBJECT_NAME(Parent_Object_ID) AS TheTable, Name
  FROM sys.objects
  WHERE OBJECTPROPERTYEX(object_id, 'IsPrimaryKey') = 1
  ORDER BY TheTable;

Here are my results:

And you could vary the type of constraint you’re interested in by substituting ‘IsUniqueCnst’, , ‘IsForeignKey’, ‘IsDefaultCnst’ or ‘IsCheckCnst’ for

‘IsPrimaryKey’ for all tables or just the ones you are interested in.

Key constraints (Primary Keys and Unique Keys)

There are only two different types of keys, a unique and primary key. A foreign key constraint is actually a constraint rather than a key.

You can list keys and their tables even more easily than we’ve already seen, by using the view specifically intended for keys.

SELECT 
    Name AS Constraint_name, 
    type,
    OBJECT_SCHEMA_NAME(parent_object_id) 
      + '.' + OBJECT_NAME(parent_object_id) AS Table_name
  FROM sys.key_constraints;

Here are my results:

It is trivial to add a WHERE clause to just list either unique or primary keys.

SELECT 
    Name AS Constraint_name,
    type,
    OBJECT_SCHEMA_NAME(parent_object_id) 
      + '.' + OBJECT_NAME(parent_object_id) AS Table_name
  FROM sys.key_constraints WHERE type='UQ';

The only key constraints are UQ (unique) or PK (primary key) so you can change the WHERE clause to ‘type=’PK’ to get the primary keys, or leave it out to get both.

But this query just tells you the name of the constraint.

You’d generally want to know what columns were part of the key. This is surprisingly easy with the Information_Schema views.

SELECT c.TABLE_SCHEMA+'.'+c.TABLE_NAME as Table_Name,
 Constraint_TYPE , c.CONSTRAINT_NAME as Constraint_Name,
 coalesce(stuff((SELECT ', ' + cc.COLUMN_NAME
  FROM Information_Schema.KEY_COLUMN_USAGE cc
  WHERE cc.CONSTRAINT_NAME=c.CONSTRAINT_NAME
    AND cc.TABLE_CATALOG=c.TABLE_CATALOG
    AND cc.TABLE_SCHEMA=c.TABLE_SCHEMA
  ORDER BY ORDINAL_POSITION
  FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,2,''), '?') AS Columns
FROM Information_Schema.TABLE_CONSTRAINTS c
WHERE CONSTRAINT_TYPE IN ('PRIMARY KEY','UNIQUE')
AND table_name IS NOT NULL;

Here are my results:

This is easy because the Information_Schema doesn’t record the type of index used to enforce a key, so the information is all in two views.

The query becomes a little more complicated than you might imagine for the catalog views. This is because SQL Server recognises that there is a difference between a key and the underlying index that enforces it. A key can be enforced by either a clustered or nonclustered index.

The columns of the actual key have to be fetched from the underlying index that enforces its uniqueness. You get this from a column called unique_index_id in the sys.key_constraints view. The index isn’t an object, but the key is. We can get it from sys.objects but prefer to use sys.key_constraints because it tells us where the underlying index is and whether its name was system-generated or not.

SELECT 
  OBJECT_SCHEMA_NAME(keys.Parent_Object_ID)
    +'.'+OBJECT_NAME(keys.Parent_Object_ID) AS TheTable,
  keys.name AS TheKey, --the name of the key
  COALESCE(STUFF(
(SELECT
   ', ' + COL_NAME(Ic.Object_Id, Ic.Column_Id)
   + CASE WHEN Is_Descending_Key <> 0 THEN ' DESC' ELSE '' END
 FROM Sys.Index_Columns Ic
 WHERE Ic.Index_Id = keys.unique_Index_Id 
   AND Ic.Object_Id = keys.parent_Object_Id
   AND is_included_column=0
ORDER BY Key_Ordinal
FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,2,''), '?') AS COLUMNS,
  REPLACE(REPLACE(LOWER(type_desc),'_',' '),'constraint','') AS [Type],
  is_system_named
FROM sys.Key_Constraints [keys]
WHERE OBJECTPROPERTYEX(keys.Parent_Object_ID,'IsUserTable')=1
ORDER BY keys.name

Here are my results:

You can find out whether your primary key is enforced by a clustered or nonclustered index in several ways. Probably the easiest is with the following query

SELECT 
  CONVERT(CHAR(40),c.Name) AS Constraint_name, 
  CONVERT(CHAR(40),OBJECT_SCHEMA_NAME(parent_object_id)
  + '.' +OBJECT_NAME(parent_object_id)) AS Table_name,
  CASE WHEN OBJECTPROPERTYEX(c.object_ID,'CnstIsClustKey')=1
    THEN 'Clustered'
ELSE 'NonClustered'
END
FROM sys.key_constraints c;

Here are my results:

As well as telling you whether the index enforcing the key is clustered or not, you can use the ObjectPropertyEx function with the following parameters to add return additional information with your query. .

The idea that a key constraint can be disabled might come as a surprise. However, if a unique index is disabled, then any PRIMARY KEY or UNIQUE constraint that it enforces, and all FOREIGN KEY constraints that reference the indexed columns are also disabled. After re-enabling the index, all constraints must be manually enabled.

There is a lot of diagnostic work that you can do to detect database problems involving key constraints. Here is an example, where you are checking whether you have any primary keys that are enforced by UNIQUE indexes that are nullable. (the question of why that is a bad idea is a separate discussion):

SELECT DISTINCT OBJECT_SCHEMA_NAME(keys.Parent_Object_ID) + '.'
                + OBJECT_NAME(keys.Parent_Object_ID) AS TheTable
  FROM sys.Key_Constraints keys
  INNER JOIN sys.Index_columns TheColumns
    ON keys.Parent_Object_ID = TheColumns.Object_ID
   AND unique_index_ID = index_ID
  INNER JOIN sys.columns c
    ON TheColumns.object_ID = c.object_ID
   AND TheColumns.column_ID = c.column_ID
  WHERE type = 'UQ' AND is_nullable = 1;

Foreign key constraints

A FOREIGN KEY constraint ensures that changes cannot be made to data in either the key (referenced) table or the foreign key (referencing) table, if those changes invalidate the relationship. Such a change will always result in an error if the foreign key is the one that is being changed. However, if it is the referenced key that is changed, you can rectify the relationship by specifying one of two actions to be performed. The DELETE action specifies that the rows in the referencing table that no longer match a row in the referenced table should be removed. The UPDATE action specifies that the rows in the referencing table should be updated to match an updated value in the referenced table. It is easy to list foreign key constraints in a database and the action specified (UPDATE, DELETE or NONE) with this query.

SELECT Name,
  OBJECT_SCHEMA_NAME(fk.parent_object_id) + '.'
  + OBJECT_NAME(fk.parent_object_id) AS TheTable,
  CASE WHEN delete_referential_action = 1 THEN 'DELETE'
    WHEN update_referential_action = 1 THEN 'UPDATE' ELSE 'NONE'
  END AS Action
  FROM sys.foreign_keys fk;

Here are my results:

Foreign Key constraints can reference any table in the same database that has a suitable key (a UNIQUE or PRIMARY KEY), or even another column in the same table (a self-reference). Here is a way of listing the self-referencing foreign key constraints:

SELECT Name as Self_Referencing_Foreign_Key,
  OBJECT_SCHEMA_NAME(fk.parent_object_id) + '.'
  + OBJECT_NAME(fk.parent_object_id) as TableName
  FROM sys.foreign_keys fk
  WHERE parent_object_id = referenced_object_id;

Here are my results:

Finding foreign key relationships

You might think that a statement such as this will display your foreign key relationships clearly

SELECT CONVERT(CHAR(80), f.name) AS foreign_key_name,
  OBJECT_SCHEMA_NAME(f.parent_object_id) + '.'
  + OBJECT_NAME(f.parent_object_id) + '.'
  + COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column,
  OBJECT_SCHEMA_NAME(f.referenced_object_id) + '.'
  + OBJECT_NAME(f.referenced_object_id) + '.'
  + COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column,
  CASE WHEN is_disabled <> 0 THEN 'Disabled' ELSE ''
  END AS is_disabled,
  delete_referential_action_desc,
  update_referential_action_desc
  FROM sys.foreign_keys AS f
  INNER JOIN sys.foreign_key_columns AS fc
    ON f.object_id = fc.constraint_object_id;

However, look what happens when you try specifying a particular table by adding a WHERE clause to the preceding query.

  WHERE f.parent_object_id = OBJECT_ID('Sales.SalesOrderDetail');

That foreign key name appears twice because it is a composite foreign key. Foreign keys will be composite when a table’s rows are distinguished via a key that has more than one column. We’ve already seen these with the key constraints, where our metadata queries have had to display one or more columns for each constraint.

If you need to create a foreign key that involves more than one column you cannot do it as part of the column definition. Basically, A FOREIGN KEY constraint specified at the column level (‘CnstlsColumn’ parameter when using the ObjectPropertyEx function will determine this) can list only one referencing column. This column must have the same data type as the column on which the constraint is defined.

If your foreign key constraint involves more than one column, you need to define it in the CREATE TABLE statement at the table-level after you’ve defined the columns. A FOREIGN KEY constraint that is specified at the table level can specify more than one column. The definition must list the columns in the table that refer to the key in the referenced table There must be the same number of columns in the referencing list as in the referenced column list. The data type of each referenced column must match the corresponding column in the referencing column list.

Here is an example from AdventureWorks where a key for the SpecialOfferProduct table consists of two columns. This is referenced by SalesOrderDetail on two columns. A foreign key constraint therefore specifies in a list both the referencing columns and the referenced columns. This diagram above represents the relationship, and the query below shows a join based on this relationship:

SELECT TOP 20 * FROM 
Sales.SalesOrderDetail INNER JOIN Sales.SpecialOfferProduct
ON Sales.SalesOrderDetail.SpecialOfferID = Sales.SpecialOfferProduct.SpecialOfferID 
AND Sales.SalesOrderDetail.ProductID = Sales.SpecialOfferProduct.ProductID 
 -- FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID

In a following section, ‘Using Foreign Key Relationships to generate code’, I’ll show how you can generate this code from the metadata, or code for any other joins from a table that has a foreign key constraint. You can remove the TOP 20 clause or change it to a different number.

You could list out just the names of all these composite foreign key constraints in your database by using this following code …

SELECT fk.name
FROM   sys.foreign_Key_columns fkc
      INNER JOIN sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id
GROUP BY fk.name
HAVING COUNT(*) > 1  

A simpler way is to use one of the special constraint properties we’ve already mentioned that are accessible via the ObjectPropertyEx() function

SELECT * FROM sys.foreign_keys
 WHERE objectpropertyex(object_id,'CnstIsColumn')=0

You now would probably want to see the list of columns for each foreign key constraint. We’ve already seen queries using Information_Schema that could be adapted to do this, such as the following.

SELECT c.TABLE_SCHEMA + '.' + c.TABLE_NAME,
  c.CONSTRAINT_NAME,
  COALESCE(STUFF(
    (
    SELECT ', ' + cc.COLUMN_NAME
    FROM Information_Schema.KEY_COLUMN_USAGE cc
      WHERE cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
        AND cc.TABLE_CATALOG = c.TABLE_CATALOG
        AND cc.TABLE_SCHEMA = c.TABLE_SCHEMA
    ORDER BY ORDINAL_POSITION
    FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)'), 1, 2, ''
  ), '?') AS COLUMNS
  FROM Information_Schema.TABLE_CONSTRAINTS c
  WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND table_name IS NOT NULL;

Here are my results:

These result tell you about the columns used by the foreign key constraints but what about the columns of the referenced table? Below is the SQL to get all the relationships for a particular table in the database, via the system views

SELECT SUBSTRING(
   (SELECT ' AND ' + Object_Schema_name(fk.Parent_object_ID) 
      + '.' + OBJECT_NAME(fk.Parent_object_ID) + '.' + cr.name + ' = ' 
      + Object_Schema_name(fkc.referenced_object_id) + '.' 
      + OBJECT_NAME(fkc.referenced_object_id) + '.' + c.NAME
    FROM    sys.foreign_Key_columns fkc
      INNER JOIN sys.columns c 
        ON fkc.referenced_column_id = c.column_id 
          AND fkc.referenced_object_id = c.object_id
      INNER JOIN sys.columns cr 
        ON fkc.parent_column_id = cr.column_id 
          AND fkc.parent_object_id = cr.object_id
    WHERE   fkc.constraint_object_id = fk.OBJECT_ID
    FOR XML PATH('')
    ), 6, 2000) + '
   -- ' + fk.name
FROM sys.foreign_keys fk
WHERE fk.referenced_object_ID = OBJECT_ID('Sales.SalesOrderDetail')
  OR fk.parent_object_id = OBJECT_ID('Sales.SalesOrderDetail')

Here are my results:

-----------------------------------------------------------------------------------
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID --FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
Sales.SalesOrderDetail.SpecialOfferID = Sales.SpecialOfferProduct.SpecialOfferID AND Sales.SalesOrderDetail.ProductID = Sales.SpecialOfferProduct.ProductID -- FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID

These results almost look like the SQL you’d use in the join clause when joining these two tables. You can certainly save yourself some time when doing routine SQL queries by using this sort of code to write the queries for you.

Using Foreign Key Relationships to generate code

We would, of course, encapsulate this sort of code in a procedure or function. Here is an example procedure that produces the SQL code illustrating all the foreign-key based joins on a particular table (or optionally, on ALL the tables in a database).

CREATE PROCEDURE FKRelationshipsFor 
@objectName SYSNAME=NULL
/*summary:   >
 
This procedure gives SQL Scripts to show sample (20 row)
results of the joins that can be made from
relationships defined in  all the foreign key constraints
associated with the table specified (all if no parameter give)
 
Author: Phil Factor
Revision: 
       – 1.1 added select statement to the ON clause
       – date: 20 sept 2010
Revision
       – 1.1 added facility to list everything
       – date: 21 sept 2010 
example:
     – code: FKRelationshipsFor 'HumanResources.Employee'
     – code: FKRelationshipsFor 'Sales.SpecialOfferProduct'
     – code: FKRelationshipsFor 'Production.ProductInventory'
     – FKRelationshipsFor –do them all
Returns:   >
single column Varchar(MAX) result called ‘Script’.
 
**/AS
IF @objectName IS NOT NULL
  IF OBJECT_ID(@objectName) IS NULL 
    BEGIN
    RAISERROR(
      'Hmm. Couldn”t find ''%s''. Have you qualified it with the Schema name?',
      16,1,@ObjectName)
    RETURN 1
    END  
 
SELECT 'SELECT TOP 20 *
FROM '  
+ Object_Schema_name(fk.referenced_object_id) 
+ '.' +OBJECT_NAME(fk.referenced_object_id)
+ '
  INNER JOIN '
+ Object_Schema_name(fk.parent_object_id) 
+ '.' +OBJECT_NAME(fk.parent_object_id)+'
    ON '+
SUBSTRING(
     (SELECT '
      AND ' + Object_Schema_name(fk.Parent_object_ID)
           + '.' + OBJECT_NAME(fk.Parent_object_ID) + '.' + cr.name + ' = ' 
           + OBJECT_NAME(fkc.referenced_object_id) + '.' + c.NAME
      FROM    sys.foreign_Key_columns fkc
              INNER JOIN sys.columns c 
                  ON fkc.referenced_column_id = c.column_id 
                     AND fkc.referenced_object_id = c.OBJECT_ID
              INNER JOIN sys.columns cr 
                  ON fkc.parent_column_id = cr.column_id 
                     AND fkc.parent_object_id = cr.OBJECT_ID
      WHERE   fkc.constraint_object_id = fk.OBJECT_ID
      FOR XML PATH('')
     ), 18, 2000) + CHAR(13)+CHAR(10)+'  –- ' + fk.name AS script
FROM sys.foreign_keys fk
WHERE fk.referenced_object_ID
     = COALESCE(OBJECT_ID(@objectName),fk.referenced_object_ID)
  OR fk.parent_object_id
     = COALESCE(OBJECT_ID(@objectName),fk.parent_object_ID) 
GO

So by then executing this procedure to see all the foreign key based joins for the Sales.SpecialOfferProduct table using this call:

  EXECUTE FKRelationshipsFor 'Sales.SpecialOfferProduct'

The procedure will produce the following SQL code as its result. Note that this code is executable.

SELECT TOP 20 *
FROM Sales.SpecialOfferProduct
  INNER JOIN Sales.SalesOrderDetail
    ON Sales.SalesOrderDetail.SpecialOfferID = SpecialOfferProduct.SpecialOfferID 
          AND Sales.SalesOrderDetail.ProductID = SpecialOfferProduct.ProductID
  -- FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID
SELECT TOP 20 *
FROM Production.Product
  INNER JOIN Sales.SpecialOfferProduct
    ON  Sales.SpecialOfferProduct.ProductID = Product.ProductID
  -- FK_SpecialOfferProduct_Product_ProductID
SELECT TOP 20 *
FROM Sales.SpecialOffer
  INNER JOIN Sales.SpecialOfferProduct
    ON Sales.SpecialOfferProduct.SpecialOfferID = SpecialOffer.SpecialOfferID
  -- FK_SpecialOfferProduct_SpecialOffer_SpecialOfferID

Check constraints

Check constraints come in two types, column-based or table-based. The only difference is that the table based constraints reference more than one column. The parent_column_id is zero if it is a table check constraint.

SELECT
  object_schema_name(Parent_Object_ID)+
    '.'+object_name(Parent_Object_ID) AS TheTable,
  name, --the name of the check constraint
  CASE WHEN parent_column_id>0 --column constraint
THEN col_name(Parent_Object_ID,parent_column_id) 
ELSE '(Table)' --0 means that it is a table constraint
  END AS Column_name,
  definition --the code that does the constraint
FROM sys.check_constraints;

Here are my results:

You can do something similar with the Information_Schema views, and in this case you can even list all the columns that are involved in the table-level check constraint. This query is more complex than it needs be because the Information_Schema views seem to be very poorly optimised and so queries have to be made rather more complicated to ensure that the number of correlated subqueries are minimised.

SELECT cc.Table_schema+'.'+cc.Table_name AS Table_Name, 
   scc.Constraint_name, Check_clause, 
   CASE WHEN count(*)=1 THEN max(column_Name)
   ELSE 
     coalesce(
  stuff((
   SELECT ', ' + multi.COLUMN_NAME
         FROM Information_Schema.CONSTRAINT_COLUMN_USAGE multi
           WHERE multi.Table_schema=cc.Table_schema
         AND multi.constraint_name=scc.constraint_name
         FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),
  1,2,''),
 '?') 
 END AS Column_names
 FROM Information_Schema.TABLE_CONSTRAINTS stc
  INNER JOIN Information_Schema.CHECK_CONSTRAINTS scc
    ON scc.constraint_schema=stc.table_schema
     AND scc.constraint_name=stc.Constraint_name
INNER JOIN Information_Schema.CONSTRAINT_COLUMN_USAGE cc
  ON cc.CONSTRAINT_NAME=stc.CONSTRAINT_NAME
   AND cc.TABLE_SCHEMA=stc.TABLE_SCHEMA
GROUP BY cc.Table_schema, cc.Table_name, scc.Constraint_name, scc.Check_clause;

Default Constraints

SELECT
  object_schema_name(Parent_Object_ID)+
    '.'+object_name(Parent_Object_ID) AS TheTable,
  name, --the name of the check constraint
  col_name(Parent_Object_ID,parent_column_id) AS Column_name,
  definition --the code that does the constraint
FROM sys.default_constraints;

Here are my results, showing all the defaults in the database:

In the Information_Schema column defaults are not constraints but the default expressions can be read from the Information_Schema.COLUMNS view. Unfortunately, you do not get the name of the default constraint because they are not considered to be constraints within the SQL Standard of the Information_Schema views.

SELECT Table_Schema+'.'+TABLE_NAME+'.'+COLUMN_NAME AS Table_and_Column,
       column_Default 
FROM Information_Schema.COLUMNS
WHERE column_default IS NOT NULL;

Here are my results:

That wraps up the basics of reporting on keys and constraints.

As a side-note, I haven’t shown how one can link to extended properties to extract comments for keys or constraints. The results were wide-enough without them, and they are, in AdventureWorks, generated automatically and therefore of little value. These may generally seem superfluous, but they have value for table-level constraints.

The following code returns a list of default constraints along with the comment from the extended properties:

SELECT
  OBJECT_NAME(Parent_Object_ID) AS TheTable,
  sys.default_constraints.name, --the name of the check constraint
  COL_NAME(Parent_Object_ID,parent_column_id) AS Column_name,
  definition, --the code that does the constraint
  CONVERT(VARCHAR(200),Value) AS The_Comment
FROM sys.default_constraints
LEFT OUTER JOIN sys.extended_properties 
ON object_id=major_id AND Minor_id=0
AND sys.extended_properties.name ='MS_Description';

Here are my results:

In the next level, we’ll be describing many system views and Information_Schema Views that will tell you about tables, their dependencies, and also about possible concerns with table design.