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

Ответ 1

попробуйте мой запрос (у меня есть pk_name и fk_name в отдельном столбце, поэтому не нужен случай), он находится на системных представлениях и быстро:

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'

Ответ 2

но он выглядит немного переполненным

Если вы хотите вытащить много значений из большого количества таблиц, тогда вы получите большой запрос. Вот как это работает. Поскольку эти вещи идут, это не так много.

Вы обеспокоены тем, что SQL Server не может справиться с этим? Не будь, может. Представление? Не так много, потому что это внутренние таблицы каталога. Варианты рефакторинга ограничены, так как вам нужен один оператор, а SP отсутствуют. Обертка его как встроенная функция, ориентированная на таблицу, может помочь, но может повредить производительность, если она пойдет не так.

Если вам просто нужна ясность в представлении SQL, подзапросы могут быть записаны как CTE, преобразованные в представления (или функции, но не) или без лишних изменений, так что все соединения имеют одинаковый уровень отступа. Последнее, скорее всего, затушевывает, чем выясняет, однако.

В целом я считаю, что ваша лучшая надежда состоит в том, чтобы написать чистый код - хороший отступ, последовательное именование, разумные псевдонимы и т.д. - и описать цели и методы в комментариях. То, что вы представили, достигает большей части этого.