Q:

SQL Server 2005+ - Get All Default and Check Constraints

I have the following query (put on my dba hat today :)) and I was wondering if anyone knows of a better way to get the Default And Check constraints without a Union All. This would sure make adding a where clause easier and more efficient.
SELECT 
    t.Name AS [TableName],
    SCHEMA_NAME(t.schema_id) AS [SchemaName], 
    c.Name AS [ColumnName],
    dc.Name AS ConstraintName,
    'DEFAULT' AS ConstraintType,
    dc.definition AS ConstraintDef
FROM sys.tables t
INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_id
UNION ALL
SELECT 
    t.Name AS [TableName],
    SCHEMA_NAME(t.schema_id) AS [SchemaName], 
    c.Name AS [ColumnName],
    cc.Name AS ConstraintName,
    'CHECK' AS ConstraintType,
    cc.definition AS ConstraintDef
FROM sys.tables t
INNER JOIN sys.check_constraints cc ON t.object_id = cc.parent_object_id
INNER JOIN sys.columns c ON cc.parent_object_id = c.object_id AND c.column_id = cc.parent_column_id

Please note that this needs to work on SQL Server 2005+ as well as azure. The current script works but doesn't work efficiently when I need to add a where clause dynamically to limit the results.

A:

You can reduce the total amount of JOINs for example like this:
with constraints as (
    select parent_object_id, parent_column_id, name, definition, 
           'DEFAULT' [ConstraintType] 
    from sys.default_constraints
    union all
    select parent_object_id, parent_column_id, name, definition, 'CHECK'
    from sys.check_constraints
)

SELECT 
    t.Name AS [TableName],
    SCHEMA_NAME(t.schema_id) AS [SchemaName], 
    c.Name AS [ColumnName],
    dc.Name AS ConstraintName,
    dc.ConstraintType,
    dc.definition AS ConstraintDef
FROM sys.tables t
INNER JOIN constraints dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id 
           AND c.column_id = dc.parent_column_id

or you can use another approach (using sys.sysconstraints and sys.objects)
SELECT 
    t.Name AS [TableName],
    SCHEMA_NAME(t.schema_id) AS [SchemaName], 
    c.Name AS [ColumnName],
    dc.Name AS ConstraintName,
    dc.ConstraintType,
    dc.definition AS ConstraintDef
FROM sys.tables t
INNER JOIN (
    SELECT object_definition(o.object_id) [definition], OBJECT_NAME(o.OBJECT_ID) [Name],
    o.parent_object_id, o.type_desc [ConstraintType], c.colid [parent_column_id]
    FROM sys.objects o
    join sys.sysconstraints c on o.object_id = c.constid
    WHERE type_desc in ('DEFAULT_CONSTRAINT', 'CHECK_CONSTRAINT')
 ) dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id 
           AND c.column_id = dc.parent_column_id

