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