sql - 如何使用 SQL 查询在 SQL Server 中列出默认约束的列？

Question

我正在使用以下查询来列出表约束（主键、唯一键、外键），但是，我很难获得外键列表及其引用（列）。

谁能建议并告诉我如何修改此代码，以便在保持 PK、FK、UQ 键相同的同时获得默认约束列的列表。

**我将需要为我的项目使用代码，因此如果您能提供完整的代码，我将不胜感激，因为我不熟悉 SQL...

这是我的原始代码

"select table_view,
    object_type, 
    constraint_type,
    constraint_name,
    details,
    fk_columns,
    referenceTable,
    pk_columns
from (
    select schema_name(t.schema_id) + '.' + t.[name] as table_view, 
        case when t.[type] = 'U' then 'Table'
            when t.[type] = 'V' then 'View'
            end as [object_type],
        case when c.[type] = 'PK' then 'Primary key'
            when c.[type] = 'UQ' then 'Unique constraint'
            when i.[type] = 1 then 'Unique clustered index'
            when i.type = 2 then 'Unique index'
            end as constraint_type, 
        isnull(c.[name], i.[name]) as constraint_name,
        substring(column_names, 1, len(column_names)-1) as [details],
        '' fk_columns,
        '' pk_columns,
        '' referenceTable
    from sys.objects t
        left outer join sys.indexes i
            on t.object_id = i.object_id
        left outer join sys.key_constraints c
            on i.object_id = c.parent_object_id 
            and i.index_id = c.unique_index_id
       cross apply (select col.[name] + ', '
                        from sys.index_columns ic
                            inner join sys.columns col
                                on ic.object_id = col.object_id
                                and ic.column_id = col.column_id
                        where ic.object_id = t.object_id
                            and ic.index_id = i.index_id
                                order by col.column_id
                                for xml path ('') ) D (column_names)
    where is_unique = 1
    and t.is_ms_shipped <> 1
    union all 
    SELECT schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
        'Table',
        'Foreign key',
        fk.name as fk_constraint_name,
        schema_name(objects.schema_id) + '.' + objects.[name] + '.' + col.[name] as details,
        col.[name] as [fk_columns],
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name,
        col2.[name] as [pk_columns]
    FROM
        sys.objects objects
        INNER JOIN sys.tables fk_tab on objects.object_id = fk_tab.object_id
        INNER JOIN sys.foreign_keys fk 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_c on fk_c.parent_object_id = fk.parent_object_id
                       and fk_c.constraint_object_id = fk.object_id
            inner join sys.columns col
                            on fk_c.parent_object_id = col.object_id
                            and fk_c.parent_column_id = col.column_id
        left join sys.columns col2
                            on fk_c.referenced_object_id = col2.object_id
                            and fk_c.referenced_column_id = col2.column_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Check constraint',
        con.[name] as constraint_name,
        con.[definition],
         '' as [fk_columns],
        '' as [pk_columns],
        '' referenceTable
    from sys.check_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Default constraint',
        con.[name],
        col.[name] + ' = ' + con.[definition],
         '' as [fk_columns],
        '' as [pk_columns],
        '' referenceTable
    from sys.default_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id) a
where table_view like 'dbo%'
order by table_view, constraint_type, constraint_name"

这是我尝试过的，但它不起作用

SQLServerException：使用 UNION、INTERSECT 或 EXCEPT 运算符组合的所有查询必须在其目标列表中具有相等数量的表达式。

"select table_view,
    object_type, 
    constraint_type,
    constraint_name,
    details,
    fk_columns,
    referenceTable,
    pk_columns,
    df_columns
from (
    select schema_name(t.schema_id) + '.' + t.[name] as table_view, 
        case when t.[type] = 'U' then 'Table'
            when t.[type] = 'V' then 'View'
            end as [object_type],
        case when c.[type] = 'PK' then 'Primary key'
            when c.[type] = 'UQ' then 'Unique constraint'
            when i.[type] = 1 then 'Unique clustered index'
            when i.type = 2 then 'Unique index'
            end as constraint_type, 
        isnull(c.[name], i.[name]) as constraint_name,
        substring(column_names, 1, len(column_names)-1) as [details],
        '' fk_columns,
        '' pk_columns,
        '' df_columns,
        '' referenceTable
    from sys.objects t
        left outer join sys.indexes i
            on t.object_id = i.object_id
        left outer join sys.key_constraints c
            on i.object_id = c.parent_object_id 
            and i.index_id = c.unique_index_id
       cross apply (select col.[name] + ', '
                        from sys.index_columns ic
                            inner join sys.columns col
                                on ic.object_id = col.object_id
                                and ic.column_id = col.column_id
                        where ic.object_id = t.object_id
                            and ic.index_id = i.index_id
                                order by col.column_id
                                for xml path ('') ) D (column_names)
    where is_unique = 1
    and t.is_ms_shipped <> 1
    union all 
    SELECT schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
        'Table',
        'Foreign key',
        fk.name as fk_constraint_name,
        schema_name(objects.schema_id) + '.' + objects.[name] + '.' + col.[name] as details,
        col.[name] as [fk_columns],
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name,
        col2.[name] as [pk_columns]
    FROM
        sys.objects objects
        INNER JOIN sys.tables fk_tab on objects.object_id = fk_tab.object_id
        INNER JOIN sys.foreign_keys fk 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_c on fk_c.parent_object_id = fk.parent_object_id
                       and fk_c.constraint_object_id = fk.object_id
            inner join sys.columns col
                            on fk_c.parent_object_id = col.object_id
                            and fk_c.parent_column_id = col.column_id
        left join sys.columns col2
                            on fk_c.referenced_object_id = col2.object_id
                            and fk_c.referenced_column_id = col2.column_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Check constraint',
        con.[name] as constraint_name,
        con.[definition],
         '' as [fk_columns],
        '' as [pk_columns],
        '' as [df_columns],
        '' referenceTable
    from sys.check_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Default constraint',
        con.[name],
        col.[name] + ' = ' + con.[definition],
         '' as [fk_columns],
        '' as [pk_columns],
        col.[name] as [df_columns],
        '' referenceTable
    from sys.default_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id) a
where table_view like 'dbo%'
order by table_view, constraint_type, constraint_name"

标签： sqlsql-serverconstraintsdefault-constraint

Answer 1

查看代码后，我意识到我可以通过修改约束名称来获取列的名称。

默认约束后，从 con.[name] 更改为 col.[name]

select schema_name(t.schema_id) + '.' + t.[name],
    'Table',
    'Default constraint',
    col.[name],
    col.[name] + ' = ' + con.[definition],
     '' as [fk_columns],
    '' as [pk_columns],
    '' referenceTable