Блог

Главная - Вопросы по программированию - Как проверить, могу ли я удалить индекс из базы данных в SQL Server

Как проверить, могу ли я удалить индекс из базы данных в SQL Server

#sql #sql-server-2005 #indexing

#sql #sql-server-2005 #индексирование

Вопрос:

После масштабной реорганизации базы данных мне нужно удалить из нее все индексы и установить новые индексы.
Я нашел хороший scrpipt для получения всех индексов и удаления, но у меня проблема — я не могу удалить индексы, созданные ограничением ПЕРВИЧНОГО КЛЮЧА. (возможно, есть и другие типы индексов, которые я не могу удалить).

Мой вопрос: как изменить приведенный ниже код, чтобы удалить все индексы, кроме индексов, созданных для первичных ключей или других, которые я не могу удалить вручную.

 DECLARE @indexName VARCHAR(128)
DECLARE @tableName VARCHAR(128)

DECLARE [indexes] CURSOR FOR
    SELECT  [sysindexes].[name] AS [Index], [sysobjects].[name] AS [Table]
    FROM [sysindexes]
    INNER JOIN [sysobjects] ON [sysindexes].[id] = [sysobjects].[id]    
    WHERE [sysindexes].[name] IS NOT NULL AND [sysobjects].[type] = 'U'

OPEN [indexes]

FETCH NEXT FROM [indexes] INTO @indexName, @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('DROP INDEX ['   @indexName   '] ON ['   @tableName   ']')
    FETCH NEXT FROM [indexes] INTO @indexName, @tableName
END

CLOSE [indexes]
DEALLOCATE [indexes]

Ответ №1:

Вы можете попробовать использовать sys.indexes представление каталога вместо устаревшего sysindexes .

В этом представлении каталога есть вызов столбца is_primary_key , поэтому вы должны быть в состоянии найти все индексы, отличные от первичного ключа, следующим образом:

 SELECT *
FROM sys.indexes
WHERE is_primary_key = 0

Вы можете легко создать свои инструкции DROP INDEX из этого:

 SELECT 'DROP INDEX '   name   ' ON '   object_name(object_id)
FROM sys.indexes
WHERE is_primary_key = 0
AND object_ID > 255     -- exclude system-level tables with object_id <= 255
AND name IS NOT NULL    -- exclude heaps with a NULL index name

Просто скопируйте и вставьте выходные данные этого оператора и запустите его — и все должно быть готово.

Комментарии:

1. Использовать эту таблицу — хороший совет, спасибо, но запускать сгенерированный ею код рискованно — он удалит также индексы из системных таблиц и вернет null для типа индекса КУЧИ — код из моего примера возвращает только индексы для пользовательских таблиц

2. ЕСЛИ вы добавите AND object_ID > 255 AND name IS NOT NULL к этому оператору, вы получите тот же результат; все таблицы «системного уровня» (id < 255) исключаются, как и кучи (где имя индекса равно NULL)

3. хорошо, спасибо — это намного лучше. Теперь есть мои индексы и несколько индексов, таких как: «queue_clustered_index» — вы знаете, что это такое?

4. @Korn — Марек К. извините, я не знаю вашей базы данных — я не могу сказать вам, что это такое ….. в инструкции DROP INDEX должно быть указано, в какой таблице определен этот индекс — проверьте эту таблицу….

Ответ №2:

Основываясь на ответе Marc_s, я нашел правильную версию скрипта:

 SELECT 'DROP INDEX '   name   ' ON '   object_name(object_id)
FROM sys.indexes
WHERE is_primary_key = 0 AND is_unique_constraint = 0
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0     -- exclude system-level tables
AND name IS NOT NULL

Проверки того, что object_Id больше 255, недостаточно:
— нам пришлось использовать OBJECTPROPERTY(object_id, ‘IsMSShipped’) = 0, потому что некоторые системные таблицы, такие как queue_messages, имеют очень высокий идентификатор

— также мы должны проверить, создан ли индекс с помощью уникального ограничения: is_unique_constraint = 0

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