Как я могу найти неиндексируемые внешние ключи в SQL Server
3 ответа
Вот ответ, который работает для SQL Server 2000, созданный коллегой:
/*
Description:
This script outputs a table with all the current database un-indexed foreign keys.
The table has three columns ( TableName , ColumnName, ForeignKeyName )
TableName: The table containing the un-indexed foreign key
ColumnName: The foreign key column that’s not indexed
ForeignKeyName: Name of foreign key witch column doesn’t have an index
*/
DECLARE
@TableName varchar(255),
@ColumnName varchar(255),
@ForeignKeyName sysname
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE FKColumns_cursor CURSOR Fast_Forward FOR
SELECT cu.TABLE_NAME, cu.COLUMN_NAME, cu.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS ic
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON ic.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE ic.CONSTRAINT_TYPE = 'FOREIGN KEY'
CREATE TABLE #temp1(
TableName varchar(255),
ColumnName varchar(255),
ForeignKeyName sysname
)
OPEN FKColumns_cursor
FETCH NEXT FROM FKColumns_cursor INTO @TableName, @ColumnName, @ForeignKeyName
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( SELECT COUNT(*)
FROM sysobjects o
INNER JOIN sysindexes x ON x.id = o.id
INNER JOIN syscolumns c ON o.id = c.id
INNER JOIN sysindexkeys xk ON c.colid = xk.colid AND o.id = xk.id AND x.indid = xk.indid
WHERE o.type in ('U')
AND xk.keyno <= x.keycnt
AND permissions(o.id, c.name) <> 0
AND (x.status&32) = 0
AND o.name = @TableName
AND c.name = @ColumnName
) = 0
BEGIN
INSERT INTO #temp1 SELECT @TableName, @ColumnName, @ForeignKeyName
END
FETCH NEXT FROM FKColumns_cursor INTO @TableName, @ColumnName, @ForeignKeyName
END
CLOSE FKColumns_cursor
DEALLOCATE FKColumns_cursor
SELECT * FROM #temp1 ORDER BY TableName
SELECT *
FROM sys.foreign_keys fk
WHERE EXISTS
(
SELECT *
FROM sys.foreign_key_columns fkc
WHERE fkc.constraint_object_id = fk.object_id
AND NOT EXISTS
(
SELECT *
FROM sys.index_columns ic
WHERE ic.object_id = fkc.parent_object_id
AND ic.column_id = fkc.parent_column_id
AND ic.index_column_id = fkc.constraint_column_id
)
)
У меня нет под рукой копии SQL Server 2000 , но вам может потребоваться изменить sys.foreign_key на sysforeignkeys и т. Д., Как описано здесь .
Этот запрос выбирает все внешние ключи, у которых нет индекса, охватывающего все столбцы, составляющие ключ.
Это поддерживает только внешние ключи с несколькими столбцами.
Это, однако, вернет ложное срабатывание, если есть составной индекс, который охватывает все столбцы, но они не являются крайними левыми столбцами в этом индексе.
Например, если есть FOREIGN KEY (col2, col3) и индекс на (col1, col2, col3) , это вернет, что индекс существует, несмотря на то, что этот индекс непригоден для этого внешнего ключа.
Во-первых: перечислить столбцы с ограничением внешнего ключа. Это поможет:
Запрос на получение всех ограничений внешнего ключа в SQL Server 2000
Перекрестное сравнение с sysindexes и syscolumns таблицами; поле keys в sysindexes содержит список всех ключей в индексе.