Мне нужен запрос T-SQL, чтобы получить все отношения в базе данных SQL Server и понять, что каждое отношение имеет несколько порядков, таких как отношения «один к одному», «один ко многим» и «многие ко многим».
Я могу получить все отношения, но не могу знать, что каждое отношение является один к одному, один ко многим.
Мой запрос таков:
SELECT
FK.[name] AS ForeignKeyConstraintName
,SCHEMA_NAME(FT.schema_id) + '.' + FT.[name] AS ForeignTable
,STUFF(ForeignColumns.ForeignColumns, 1, 2, '') AS ForeignColumns
,SCHEMA_NAME(RT.schema_id) + '.' + RT.[name] AS ReferencedTable
,STUFF(ReferencedColumns.ReferencedColumns, 1, 2, '') AS ReferencedColumns,
'one-to-one or one-to-many or many-to-many' as RelationType
FROM
sys.foreign_keys FK
INNER JOIN
sys.tables FT ON FT.object_id = FK.parent_object_id
INNER JOIN
sys.tables RT ON RT.object_id = FK.referenced_object_id
CROSS APPLY
(SELECT
', ' + iFC.[name] AS [text()]
FROM
sys.foreign_key_columns iFKC
INNER JOIN
sys.columns iFC ON iFC.object_id = iFKC.parent_object_id
AND iFC.column_id = iFKC.parent_column_id
WHERE
iFKC.constraint_object_id = FK.object_id
ORDER BY
iFC.[name]
FOR XML PATH('')) ForeignColumns (ForeignColumns)
CROSS APPLY
(SELECT
', ' + iRC.[name] AS [text()]
FROM
sys.foreign_key_columns iFKC
INNER JOIN
sys.columns iRC ON iRC.object_id = iFKC.referenced_object_id
AND iRC.column_id = iFKC.referenced_column_id
WHERE
iFKC.constraint_object_id = FK.object_id
ORDER BY
iRC.[name]
FOR XML PATH('')) ReferencedColumns (ReferencedColumns)
Что я могу сделать ?
@ThomA этот вопрос не зависит от дизайна базы данных! это после того, как дизайн завершен
Отношения между вашими таблицами являются частью дизайна базы данных.
@ThomA это после завершения проектирования, в коде C# мне нужно это знать! например, когда-то вам нужно что-то обрабатывать во время выполнения! на самом деле я разрабатываю программу генерации классов на основе базы данных, аналогичную Entity Framework Scaffolder.
Звучит сложно. Вы можете проверить столбцы на обеих «сторонах», чтобы увидеть, являются ли они сами по себе уникальными с обеих сторон, но не знаете, как вы будете обрабатывать «таблицы соединения» для многих ко многим.
Какая у вас версия SQL Server?
@siggemannen Да, ты говоришь правду!
Решение заключается в использовании "INFORMATION_SCHEMA" с запросом на получение всех ограничений, и в результате REFERENCED_TABLE_NAME является одной стороной, а FK_TABLE_NAME - многосторонней связью.
Запрос:
`SELECT
KCU1.TABLE_SCHEMA AS FK_TABLE_SCHEMA
,KCU1.TABLE_NAME AS FK_TABLE_NAME
,KCU1.COLUMN_NAME AS FK_COLUMN_NAME
,KCU2.TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA
,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2
ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
order by KCU2.TABLE_NAME`
Объединение «многие: многие» будет отображаться так же, как и «1: много», только несколько раз dbfiddle.uk/VQeVkBvK, поэтому на самом деле это не отвечает на ваш вопрос.
На самом деле невозможно показать отношение «многие ко многим», показав каждый внешний ключ в каждой таблице отдельно, поскольку для «многие ко многим» требуются два отдельных отношения из одной таблицы.
Вы можете проверить, является ли отношение «один к одному» или «многие к одному», проверив, есть ли уникальный индекс во внешней таблице, который соответствует точным столбцам внешнего ключа (ни больше, ни меньше).
Самый простой способ сделать это, вероятно, просто объединить внешние ключи в массив JSON, а затем разбить его обратно, используя OPENJSON
.
SELECT
FK.name AS ForeignKeyConstraintName,
FS.name AS ForeignTableSchema,
FT.name AS ForeignTable,
Columns.ForeignColumns,
RS.name AS ReferencedTableSchema,
RT.name AS ReferencedTable,
Columns.ReferencedColumns,
IIF(i.isUniqueColumnSet = 1, 'one-to-one', 'many-to-one') as RelationType
FROM sys.foreign_keys FK
JOIN sys.tables FT ON FT.object_id = FK.parent_object_id
JOIN sys.schemas FS ON FS.schema_id = FT.schema_id
JOIN sys.tables RT ON RT.object_id = FK.referenced_object_id
JOIN sys.schemas RS ON RS.schema_id = RT.schema_id
CROSS APPLY (
SELECT
ForeignColumns = STRING_AGG(FC.name, ', ')
WITHIN GROUP (ORDER BY FKC.constraint_column_id),
ForeignColumnIds = '[' + STRING_AGG(FKC.parent_column_id, ', ')
WITHIN GROUP (ORDER BY FKC.constraint_column_id) + ']',
ReferencedColumns = STRING_AGG(RC.name, ', ')
WITHIN GROUP (ORDER BY FKC.constraint_column_id),
ColumnCount = COUNT(*)
FROM sys.foreign_key_columns FKC
JOIN sys.columns FC
ON FC.object_id = FKC.parent_object_id
AND FC.column_id = FKC.parent_column_id
JOIN sys.columns RC
ON RC.object_id = FKC.referenced_object_id
AND RC.column_id = FKC.referenced_column_id
WHERE FKC.constraint_object_id = FK.object_id
) Columns
OUTER APPLY (
SELECT TOP (1)
isUniqueColumnSet = MAX(1)
FROM sys.indexes i
JOIN sys.index_columns ic
ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
LEFT JOIN OPENJSON(Columns.ForeignColumnIds) ids
ON CAST(ids.value AS int) = ic.column_id
WHERE i.object_id = FT.object_id
AND i.is_unique = 1
GROUP BY
i.index_id
HAVING COUNT(*) = Columns.ColumnCount
AND COUNT(*) = COUNT(CAST(ids.value AS int))
) i;
Логика OUTER APPLY
в основном представляет собой реляционное деление:
index_id
Это, на мой взгляд, когда важна документация по дизайну вашей базы данных.