SQL查询获取杂项列信息

Question

我需要编写一个查询，在其中可以获得有关所有列(具有数据类型)的信息，并了解哪些列是PK/FK。对于FK，需要附加信息，例如它来自哪个其他表。 我已经得到了有效的查询，但它看起来有点矫枉过正。

这可以做得更好吗？ 我不喜欢其中的子查询连接。它必须是一个查询，不能通过 SP 完成。

我的示例是针对 Northwind(以及我正在测试的一些附加 FK 关系)

SELECT 
    t.name AS TableName, 
    t.object_id AS TableObjectId,
    tCols.column_name AS ColumnName, 
    tCols.data_type AS ColumnDataType, 
    ISNULL(tCols.numeric_scale, 0) AS ColumnDecimalPlaces,
    CASE tConstraints.CONSTRAINT_TYPE
        WHEN 'PRIMARY KEY'
            THEN '1'
            ELSE '0'
    END AS ISPK, 
    CASE tConstraints.CONSTRAINT_TYPE
        WHEN 'FOREIGN KEY'
            THEN '1'
            ELSE '0'
    END AS ISFK, 
    tConstraints.CONSTRAINT_TYPE,
    tConstraints.CONSTRAINT_NAME,
    fkInfo.FK_name,
    fkInfo.PK_column,
    fkInfo.PK_table,
    fkInfo.PK_name
FROM sys.objects t
LEFT JOIN information_schema.columns tCols ON tCols.TABLE_NAME = t.name 
LEFT JOIN (
    SELECT  
        tc.CONSTRAINT_NAME, 
        tc.TABLE_NAME, 
        tc.CONSTRAINT_TYPE, 
        kcu.COLUMN_NAME
    FROM information_schema.table_constraints tc
    INNER JOIN  information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
) AS tConstraints
     ON t.name = tConstraints.TABLE_NAME
    AND tCols.column_name = tConstraints.COLUMN_NAME
LEFT JOIN (
    SELECT
        o1.name AS FK_table,
        c1.name AS FK_column,
        fk.name AS FK_name,
        o2.name AS PK_table,
        c2.name AS PK_column,
        pk.name AS PK_name
    FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.foreign_key_columns fkc
        ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c1
         ON fkc.parent_object_id = c1.object_id
        AND fkc.parent_column_id = c1.column_id
    INNER JOIN sys.columns c2
         ON fkc.referenced_object_id = c2.object_id
        AND fkc.referenced_column_id = c2.column_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
         ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
) AS fkInfo ON t.name = fkInfo.FK_table
    AND tCols.column_name = fkInfo.FK_column
WHERE t.name = 'Products'
ORDER BY 3

Answer 1

尝试我的查询(我在单独的列中有 pk_name 和 fk_name，所以不需要大小写)，它位于系统 View 上，而且速度很快:

with 
  pk as (select pki.object_id, pki.column_id, _pk.name 
      from sys.index_columns pki  
      join sys.key_constraints _pk 
      on _pk.unique_index_id = pki.index_id and _pk.parent_object_id = pki.object_id
      where 1=1),
  fk as (select fkc.parent_object_id, fkc.parent_column_id, fk.name name, pkt.name pk_table, pkc.name pk_column, pkc.object_id, pkc.column_id
    from sys.foreign_keys as fk
    join sys.tables pkt
    on pkt.object_id = fk.referenced_object_id
    join sys.foreign_key_columns as fkc
    on fkc.constraint_object_id = fk.object_id
    join sys.columns as pkc
    on pkc.object_id = fkc.referenced_object_id and pkc.column_id = fkc.referenced_column_id
    where 1=1)
select t.name TableName
, t.object_id TableObjectId
, c.column_id CId
, c.name AS ColumnName
, typ.name AS ColumnDataType
, c.is_identity
, c.precision
, c.scale
, pk.name pk_name
, fk.name fk_name
, fk.pk_table
, fk.pk_column
, fkpk.name pk_for_fk
from sys.tables as t
inner join sys.columns as c on t.object_id = c.object_id
inner join sys.types as typ on typ.user_type_id = c.user_type_id
left join pk on pk.object_id = t.object_id and pk.column_id = c.column_id
left join fk on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
left join pk as fkpk on fkpk.object_id = fk.object_id and fkpk.column_id = fk.column_id
WHERE t.name = 'Products'