如何将这两个查询合并在一起？

Question

我已经编写了一个基于数据库(Tracker)和表(Work)查找列元数据的查询：

SELECT      Cols.Name,
            TYPE_NAME(Cols.user_type_id) Type,
            CAST(ISNULL(OBJECTPROPERTY(OBJECT_ID(Keys.CONSTRAINT_NAME), 'IsPrimaryKey'), 0) AS BIT) PrimaryKey,
            Cols.is_identity [Identity],
            Cols.is_nullable Nullable,
            CAST(ColumnProperty(object_id, Cols.name, 'IsComputed') AS BIT) Computed,
            CASE WHEN Cons.CONSTRAINT_TYPE IS NULL THEN NULL ELSE Keys.CONSTRAINT_NAME END ForeignKey
FROM        sys.columns Cols

LEFT JOIN   INFORMATION_SCHEMA.KEY_COLUMN_USAGE Keys
ON          Keys.COLUMN_NAME = Cols.name
AND         Keys.TABLE_CATALOG = 'Tracker'
AND         Keys.TABLE_NAME = 'Work'

LEFT JOIN   INFORMATION_SCHEMA.TABLE_CONSTRAINTS Cons
ON          Cons.CONSTRAINT_NAME = Keys.CONSTRAINT_NAME
AND         Cons.CONSTRAINT_TYPE = 'FOREIGN KEY'

WHERE       Cols.object_id = OBJECT_ID('Work')
ORDER BY    Cols.column_id ASC

这会产生这样的结果：

​

​

我还编写了这个查询来查找外键的关系信息：

SELECT      OBJECT_NAME(FKCols.referenced_object_id) ForeignTable,
            Cols.name ForeignColumn
FROM        sys.foreign_key_columns FKCols

LEFT JOIN   sys.foreign_keys FKs
ON          FKCols.constraint_object_id = FKs.object_id

LEFT JOIN   sys.columns Cols
ON          Cols.object_id = FKs.referenced_object_id

WHERE       Cols.column_id = FKCols.referenced_column_id
AND         FKs.name = 'FK_Work_Tasks'

这会产生这样的结果：

​

​

我花了几个小时才走到这一步，但我真的被卡住了。我试图得到这样的结果：

​

​

如何将这些查询合并在一起？

我打算把这篇文章发布到DBA上，但我不确定这是属于基本的还是高级的SQL查询(离题/开题)。

此外，如果有人能指出任何我可以在这里使用的优化，那也是非常感谢的！

Answer 1

请注意，一个键可以由多列组成。不能说列是主键，只能说列是键的一部分。主键和外键都是如此。

下面是一个仅使用sys.*视图的示例：

select  col.name
,       col_type.name
,       case when tab_pk_col.column_id is not null then 1 else 0 end as PartOfPrimaryKey
,       col.is_identity as [Identity]
,       col.is_nullable as Nullable
,       col.is_computed as Computed
,       tab_fk.name as PartOfForeignKey
,       ref.name as ForeignTable
,       ref_col.name as ForeignColumn
from    sys.tables tab -- Examined table
left join sys.columns col -- Columns of examined table
on      col.object_id = tab.object_id
left join sys.types col_type -- Type of column
on      col.system_type_id = col_type.system_type_id
left join sys.key_constraints tab_pk -- Primary keys
on      tab_pk.parent_object_id = tab.object_id
        and tab_pk.type = 'pk'
left join sys.index_columns tab_pk_col -- Columns in primary key
on      tab_pk_col.object_id = tab_pk.parent_object_id
        and tab_pk_col.index_id = tab_pk.unique_index_id
        and tab_pk_col.column_id = col.column_id
left join sys.foreign_key_columns tab_fk_col -- Columns in foreign keys
on      tab_fk_col.parent_object_id = tab.object_id
        and tab_fk_col.parent_column_id = col.column_id
left join sys.foreign_keys tab_fk -- Foreign keys
on      tab_fk.object_id = tab_fk_col.constraint_object_id
        and tab_fk.type = 'f'
left join sys.columns ref_col -- Columns referenced by column in foreign key
on      ref_col.object_id = tab_fk_col.referenced_object_id
        and ref_col.column_id = tab_fk_col.referenced_column_id
left join sys.tables ref -- Table name of referenced column
on      ref.object_id = ref_col.object_id
where   tab.name = 'table3'

See it working at SQL Fiddle.

Answer 2

要获取所需的外列数据(ForeignTable和ForeignColumn)，可以在sys.foreign_key_columns上使用稍微复杂一点的连接来获得这两个数据。

修改的SQL

SELECT      Cols.Name,
            TYPE_NAME(Cols.user_type_id) Type,
            CAST(ISNULL(OBJECTPROPERTY(OBJECT_ID(Keys.CONSTRAINT_NAME), 'IsPrimaryKey'), 0) AS BIT) PrimaryKey,
            Cols.is_identity [Identity],
            Cols.is_nullable Nullable,
            --specified Cols.object_id here to remove ambiguous column error
            CAST(ColumnProperty(Cols.object_id, Cols.name, 'IsComputed') AS BIT) Computed, 
            CASE WHEN Cons.CONSTRAINT_TYPE IS NULL THEN NULL ELSE Keys.CONSTRAINT_NAME END ForeignKey,
            --new FK info columns
            OBJECT_NAME(FKCols.referenced_object_id) ForeignTable,
            FKColsInfo.name as ForeignColumn
FROM        sys.columns Cols

LEFT JOIN   INFORMATION_SCHEMA.KEY_COLUMN_USAGE Keys
ON          Keys.COLUMN_NAME = Cols.name
AND         Keys.TABLE_CATALOG = 'Tracker'
AND         Keys.TABLE_NAME = 'Work'

LEFT JOIN   INFORMATION_SCHEMA.TABLE_CONSTRAINTS Cons
ON          Cons.CONSTRAINT_NAME = Keys.CONSTRAINT_NAME
AND         Cons.CONSTRAINT_TYPE = 'FOREIGN KEY'

--JOIN that will fetch both the foreign key table name and the associated column name in that table
LEFT JOIN   (
                sys.foreign_key_columns FKCols LEFT JOIN sys.columns FKColsInfo 
                ON FKColsInfo.object_id = FKCols.referenced_object_id
                AND FKColsInfo.column_id = FKCols.referenced_column_id
            )
ON          FKCols.parent_object_id = Cols.object_id
AND         FKCols.parent_column_id = Cols.column_id

WHERE       Cols.object_id = OBJECT_ID('Work')
ORDER BY    Cols.column_id ASC