问如何从Information_Schema.Columns获取列的主键或键约束详细信息？

EN

USE AdventureWorks2014
GO
;WITH DetailInfo
AS(
SELECT 
    o.name TableName,
    c.name ColumnName,
    t.Name DataType,
    c.max_length MaxLength,
    c.precision Precision,
    c.scale Scale,
    c.is_nullable IsNull,
    ISNULL(i.is_primary_key, 0) 'PrimaryKey',
    ISNULL(i.is_unique_constraint, 0) 'UniqueKey',
    ISNULL(i.name, 0) 'IndexName',
    ISNULL(i.type_desc, 0) 'IndexType',
    ISNULL(i.is_disabled, 0) 'IndexDisabled'
FROM  sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
)
SELECT * FROM DetailInfo
WHERE TableName = 'Employee'

USE AdventureWorks2014
GO
;WITH ContraintDetails 
AS(
--KEY_CONSTRAINT
SELECT schema_name(o.schema_id) + '.' + o.[name] TableName,   
        c.name as ColumnName,
        k.type_desc ConstantType,
        k.[name] ConstantName,
        'Is Enforced :' + CASE WHEN  k.is_enforced = 1 THEN 'YES' ELSE 'NO' END Details 
FROM sys.key_constraints  k
INNER JOIN sys.all_columns a ON k.parent_object_id = a.object_id
INNER JOIN sys.columns c ON a.object_id = c.object_id AND a.column_id = c.column_id
INNER JOIN sys.objects o ON  c.object_id = o.object_id

UNION ALL
--DEFAULT_CONSTRAINT
SELECT schema_name(t.schema_id) + '.' + t.[name] TableName,   
        c.name as ColumnName,
        con.type_desc ConstantType,
        con.[name] ConstantName,
        col.[name] + ' : ' + con.[definition] Details
FROM sys.default_constraints con
INNER JOIN sys.objects t on con.parent_object_id = t.object_id
INNER JOIN sys.all_columns col on con.parent_column_id = col.column_id and con.parent_object_id = col.object_id
INNER JOIN sys.columns c ON col.object_id = c.object_id AND col.column_id = c.column_id

UNION ALL

-- FOREIGN_KEY_CONSTRAINT
SELECT schema_name(fk_tab.schema_id) + '.' + fk_tab.name as TableName,
c.name as ColumnName,
fk.type_desc ConstraintType,
fk.name as ConstraintName,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name Details
from sys.foreign_keys fk
INNER JOIN sys.objects o ON fk.parent_object_id = o.object_id
INNER JOIN sys.tables fk_tab on fk_tab.object_id = fk.parent_object_id
INNER JOIN sys.tables pk_tab on pk_tab.object_id = fk.referenced_object_id
INNER JOIN sys.foreign_key_columns fk_cols on fk_cols.constraint_object_id = fk.object_id
INNER JOIN sys.columns c ON fk_cols.parent_object_id = c.object_id AND fk_cols.parent_column_id = c.column_id

UNION ALL

--CHECK_CONSTRAINT
SELECT schema_name(t.schema_id) + '.' + t.[name] TableName,
c.name as ColumnName,
con.type_desc  ConstraintType,
con.[name] as constraint_name,
con.[definition] Details
FROM sys.check_constraints con
INNER JOIN sys.objects t on con.parent_object_id = t.object_id
INNER JOIN sys.all_columns col on con.parent_column_id = col.column_id and con.parent_object_id = col.object_id
INNER JOIN sys.columns c ON col.object_id = c.object_id AND col.column_id = c.column_id

UNION ALL
-- INDEX
SELECT schema_name(o.schema_id) + '.' + o.[name] TableName,
    c.name as ColumnName,
    i.type_desc ConstantType,
    i.[name] ConstantName,
    'Is Disabled :' + CASE WHEN  i.is_disabled = 1 THEN 'YES' ELSE 'NO' END Details
FROM sys.indexes  i
INNER JOIN sys.index_columns ic ON  i.object_id = ic.object_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.objects o ON  c.object_id = o.object_id
)
SELECT * FROM ContraintDetails
WHERE TableName = 'HumanResources.Employee' AND  ColumnName = 'BirthDate'

ORDER BY TableName,ColumnName