Just Code‎ > ‎

SQL - Find all constraints for a table

posted May 13, 2011, 1:37 AM by Peter Henell
with including columns

CREATE PROCEDURE dbo.ConstraintCheck @tableName VARCHAR(250)
AS

SELECT 
        is_primary_key AS IsPrimaryKey
        , is_unique_constraint AS IsUnique
        , OBJECT_NAME(i.object_id) AS TableName
        , c.name AS ColumnName
        , TYPE_NAME(c.system_type_id) AS DataType
        , c.is_identity AS IsIdentity
        , i.name AS ConstraintName
        , ic.index_id AS IndexId
    FROM 
        sys.indexes I 
    INNER JOIN sys.index_columns ic
        ON i.object_id = ic.object_id AND I.index_id = ic.index_id
    INNER JOIN sys.columns c
        ON i.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE 
        i.object_id = OBJECT_ID(@tableName)
        AND is_included_column = 0 
        and Is_Disabled = 0
    ORDER BY 
        IndexId

Comments