SQL Query для получения информации о столбце

Мне нужно было написать запрос, где я могу получить информацию обо всех столбцах (с типом данных), а также узнать, какие из них 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

Ответы

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'