Получить профессию в Skypro
SQL Server 2008: как получить ограничения всех таблиц
Пройдите тест, узнайте какой профессии подходите
Быстрый ответ
Чтобы быстро узнать об ограничениях определённой таблицы, отправьте запрос к INFORMATION_SCHEMA.TABLE_CONSTRAINTS, заменив 'YourTable' на имя своей таблицы:
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'YourTable';Этот запрос даст вам возможность быстро увидеть названия и типы всех ограничений, вроде PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK.
Расширенный запрос к ограничениям
Для более детализированного анализа ограничений можно воспользоваться представлениями системного каталога sys из SQL Server 2008. Пример ниже демонстрирует, как собрать информацию о списках таблиц, их колонках и применённых к ним ограничениях:
SELECT
t.name AS TableName,
c.name AS ColumnName,
ty.name AS TypeName,
dc.name AS DefaultConstraintName,
cc.name AS CheckConstraintName,
fk.name AS ForeignKeyConstraintName,
pk.name AS PrimaryKeyConstraintName
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
LEFT JOIN sys.default_constraints dc ON c.object_id = dc.parent_object_id AND c.column_id = dc.parent_column_id
LEFT JOIN sys.check_constraints cc ON c.object_id = cc.parent_object_id AND c.column_id = cc.parent_column_id
LEFT JOIN sys.foreign_key_columns fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
LEFT JOIN sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id
LEFT JOIN sys.key_constraints pk ON t.object_id = pk.parent_object_id AND pk.type = 'PK'
ORDER BY TableName, ColumnName;Этот обширный запрос позволяет провести глубокий анализ, результаты которого представлены в виде удобного для чтения списка, отсортированного по имени таблицы и колонок.
Определение типов ограничений
Точное определение типов ограничений
Вы можете уточнить типы ограничений, применяя новые системные представления sys, демонстрирующие более ясное представление с использованием атрибута type_desc. Следующий код помогает лучше разобраться с типами ограничений:
SELECT
obj.name AS ConstraintName,
CASE obj.type_desc
WHEN 'PRIMARY_KEY_CONSTRAINT' THEN 'PK'
WHEN 'FOREIGN_KEY_CONSTRAINT' THEN 'FK'
WHEN 'CHECK_CONSTRAINT' THEN 'CK'
WHEN 'DEFAULT_CONSTRAINT' THEN 'DF'
WHEN 'UNIQUE_CONSTRAINT' THEN 'UQ'
END AS ConstraintType
FROM sys.objects obj
WHERE obj.type_desc LIKE '%_CONSTRAINT'
ORDER BY ConstraintType, ConstraintName;Этот запрос позволит классифицировать ограничения в соответствии с их типами.
Подсчёт ограничений для каждой таблицы
Для подсчёта количества ограничений на каждой таблице используйте следующий запрос:
SELECT
TableName = t.name,
ConstraintCount = COUNT(*)
FROM sys.tables t
JOIN sys.objects obj ON t.object_id = obj.parent_object_id AND obj.type_desc LIKE '%_CONSTRAINT'
GROUP BY t.name
ORDER BY ConstraintCount DESC;Этот запрос сформирует список таблиц с количеством наложенных на них ограничений, отсортированный от большего к меньшему числу ограничений.
Визуализация
Можно представить ограничения таблицы в качестве охранников сейфа:
Сейф (🏰) = Таблица
Сокровище (💎) = Данные
| Охранник 🛡️ | Тип ограничения |
| ------------------------- | --------------------------- |
| Первичный ключ | 🔑 Контролирует уникальный доступ |
| Внешний ключ | 🌉 Соединяет два сейфа |
| Проверка | 📜 Подтверждает ценность сокровищ |
| По умолчанию | 🎁 Дарит новое сокровище |
| Уникальный | 🦄 Обеспечивает редкость сокровищ |Детальное исследование внешних ключей
Укрепление целостности данных
Внешние ключи обеспечивают сохранность данных между связанными таблицами. Используйте следующий запрос, чтобы проиллюстрировать взаимосвязь внешних ключей:
SELECT
OBJECT_NAME(fk.object_id) AS ForeignKey,
OBJECT_SCHEMA_NAME(fk.parent_object_id) AS PrimaryTableSchema,
OBJECT_NAME(fk.parent_object_id) AS PrimaryTable,
COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS PrimaryColumn,
OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS ReferenceTableSchema,
OBJECT_NAME(fk.referenced_object_id) AS ReferenceTable,
COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferenceColumn
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
ORDER BY PrimaryTable, ForeignKey;Поиск таблиц без ограничений
Чтобы найти таблицы, которые не имеют никаких ограничений, можно воспользоваться следующим запросом:
SELECT
t.name AS TableName,
(SELECT count(*) FROM sys.objects o WHERE t.object_id = o.parent_object_id AND o.type_desc LIKE '%_CONSTRAINT') AS ConstraintCount
FROM sys.tables t
HAVING COUNT(ConstraintCount) = 0
ORDER BY TableName;Этот запрос поможет вам определить зоны с потенциальной угрозой для вашей базы данных.
Полезные материалы
- sys.objects (Transact-SQL) – SQL Server | Microsoft Learn — Официальный справочник по системным представлениям каталогов с полной информацией об ограничениях.
- SQL Server: Внешние ключи – TechOnTheNet — Обстоятельное руководство по работе с ограничениями внешних ключей в SQL Server.
- TABLE_CONSTRAINTS (Transact-SQL) – SQL Server | Microsoft Learn — Официальное руководство по формированию запросов к информации об ограничениях таблиц.
- sql – Как получить список внешних ключей таблицы – Stack Overflow — Обсуждение на Stack Overflow о том, как составить SQL-запрос для получения списка внешних ключей таблицы.