Sql Server 2008- Get table constraints

Advanced constraints query

Crafting more complex queries is simple with the SQL Server 2008's sys catalog views. For instance, one can gather a list of tables, their columns, and attached constraints as shown below:

-- Well, gathering all that gossip about tables and constraints.
SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    ty.name AS TypeName,
    dc.name AS DefaultConstraintName,   -- Even defaults have names.
    cc.name AS CheckConstraintName,    -- Checking in on the checks.
    fk.name AS ForeignKeyConstraintName, -- Not related, it's a foreign name.
    pk.name AS PrimaryKeyConstraintName -- Keeping it primary.
FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    LEFT JOIN sys.default_constraints dc ON c.object_id = dc.parent_object_id AND c.column_id = dc.parent_column_id
    LEFT JOIN sys.check_constraints cc ON c.object_id = cc.parent_object_id AND c.column_id = cc.parent_column_id
    LEFT JOIN sys.foreign_key_columns fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
    LEFT JOIN sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id
    LEFT JOIN sys.key_constraints pk ON t.object_id = pk.parent_object_id AND pk.type = 'PK'
ORDER BY TableName, ColumnName;


This comprehensive query leverages sys views such as sys.tables, sys.columns, sys.default_constraints, sys.check_constraints, sys.foreign_keys, and sys.key_constraints for an in-depth overview. The result? A well-ordered list by table and column names.

Constraint types identification

Pinpointing constraint types

There's a neat trick around constraint types. In old school terms, this would involve xtype from sysobjects. However, the latest sys views offer more clarity through the type_desc attribute. To illustrate, here's a practical way to understand constraint types:

-- A little lesson in constraint-naming conventions.
SELECT 
    obj.name AS ConstraintName,
    CASE obj.type_desc
        WHEN 'PRIMARY_KEY_CONSTRAINT' THEN 'PK'
        WHEN 'FOREIGN_KEY_CONSTRAINT' THEN 'FK'
        WHEN 'CHECK_CONSTRAINT' THEN 'CK'
        WHEN 'DEFAULT_CONSTRAINT' THEN 'DF'
        WHEN 'UNIQUE_CONSTRAINT' THEN 'UQ'
    END AS ConstraintType
FROM sys.objects obj
WHERE obj.type_desc LIKE '%_CONSTRAINT'
ORDER BY ConstraintType, ConstraintName;


This snippet speaks constraint language by categorizing constraints according to their types.

Counting constraints per table

And in the spirit of going the extra mile, let's handle multiple constraints per table like pros:

-- Who said counting was only for sheep?
SELECT 
    TableName = t.name,
    ConstraintCount = COUNT(*)
FROM sys.tables t
    JOIN sys.objects obj ON t.object_id = obj.parent_object_id AND obj.type_desc LIKE '%_CONSTRAINT'
GROUP BY t.name
ORDER BY ConstraintCount DESC;


This code delivers a concise report indicating how many constraints each table exhibits. It arranges the tables from the most to the least constrained.

Detailed exploration of foreign keys

Reinforcing data integrity

Foreign keys are safeguards maintaining consistency across referenced columns. Let's map foreign keys and their dependencies:

SELECT 
    OBJECT_NAME(fk.object_id) AS ForeignKey, -- It's a foreign thing!
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS PrimaryTableSchema,
    OBJECT_NAME(fk.parent_object_id) AS PrimaryTable,
    COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS PrimaryColumn,
    OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS ReferenceTableSchema,
    OBJECT_NAME(fk.referenced_object_id) AS ReferenceTable,
    COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferenceColumn
FROM sys.foreign_keys AS fk
    INNER JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
ORDER BY PrimaryTable, ForeignKey;


This query offers a thorough depiction of foreign key relationships, just in one glance.

Identifying constraint-less tables

At times, you might find tables devoid of constraints. Fear not, these can be spotted easily:

SELECT 
    t.name AS TableName,
    (SELECT count(*) FROM sys.objects o WHERE t.object_id = o.parent_object_id AND o.type_desc LIKE '%_CONSTRAINT') AS ConstraintCount
FROM sys.tables t
HAVING COUNT(ConstraintCount) = 0
ORDER BY TableName;


A quick run of this query guides you to potential unprotected parts of your database that may need extra attention.