Как я могу отбросить все индексы в базе данных SQL с помощью одной команды?
Итак, как я могу отбросить все индексы в базе данных SQL с помощью одной команды? У меня есть эта команда, которая получит мне все 20 или около того отбрасывающих операторов, но как я могу запустить все эти инструкции сброса из этого "набора результатов"?
select * from vw_drop_idnex;
Другой вариант, который дает мне тот же список:
SELECT 'DROP INDEX ' + ix.Name + ' ON ' + OBJECT_NAME(ID) AS QUERYLIST
FROM sysindexes ix
WHERE ix.Name IS NOT null and ix.Name like '%pre_%'
Я попытался сделать "exec (select cmd из vw_drop_idnex)", и это не сработало. Я ищу что-то, что работает как цикл for и запускает запросы один за другим.
-----------------------
С помощью Rob Farleys окончательный вариант script:
declare @ltr nvarchar(1024);
SELECT @ltr = ( select 'alter table '+o.name+' drop constraint '+i.name+';'
from sys.indexes i join sys.objects o on i.object_id=o.object_id
where o.type<>'S' and is_primary_key=1
FOR xml path('') );
exec sp_executesql @ltr;
declare @qry nvarchar(1024);
select @qry = (select 'drop index '+o.name+'.'+i.name+';'
from sys.indexes i join sys.objects o on i.object_id=o.object_id
where o.type<>'S' and is_primary_key<>1 and index_id>0
for xml path(''));
exec sp_executesql @qry
Ответы
Ответ 1
Вы очень близки.
declare @qry nvarchar(max);
select @qry =
(SELECT 'DROP INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ID) + '; '
FROM sysindexes ix
WHERE ix.Name IS NOT null and ix.Name like '%prefix_%'
for xml path(''));
exec sp_executesql @qry
Ответ 2
это сработало для меня, мы пропускаем системные индексы и ограничения
declare @qry nvarchar(max);
select @qry = (
select 'IF EXISTS(SELECT * FROM sys.indexes WHERE name='''+ i.name +''' AND object_id = OBJECT_ID(''['+s.name+'].['+o.name+']'')) drop index ['+i.name+'] ON ['+s.name+'].['+o.name+']; '
from sys.indexes i
inner join sys.objects o on i.object_id=o.object_id
inner join sys.schemas s on o.schema_id = s.schema_id
where o.type<>'S' and is_primary_key<>1 and index_id>0
and s.name!='sys' and s.name!='sys' and is_unique_constraint=0
for xml path(''));
exec sp_executesql @qry
Ответ 3
От: Стивен Хилл Bloggie
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'
--AND [sysindexes].[indid] > 1
OPEN [indexes]
FETCH NEXT FROM [indexes] INTO @indexName, @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'DROP INDEX [' + @indexName + '] ON [' + @tableName + ']'
Exec ('DROP INDEX [' + @indexName + '] ON [' + @tableName + ']')
FETCH NEXT FROM [indexes] INTO @indexName, @tableName
END
CLOSE [indexes]
DEALLOCATE [indexes]
GO
Ответ 4
Незначительные улучшения принятого ответа, которые я должен был сделать в своем собственном случае, в основном для учета схем:
declare @qry nvarchar(4000);
select @qry = (select 'drop index ['+s.name+'].['+o.name+'].['+i.name+'];'
from sys.indexes i join sys.objects o on i.object_id=o.object_id join sys.schemas s on o.schema_id=s.schema_id
where o.type<>'S' and is_primary_key<>1 and index_id>0 and s.name<>'sys'
for xml path(''));
exec sp_executesql @qry
Также: В моем случае он не мог работать за один раз, потому что script становится длиннее 4000 символов. Единственный способ, с которым я мог подумать, - это поместить "верх 20" во внутренний выбор и выполнить его несколько раз.
Ответ 5
Ни один из ответов не вполне соответствовал моим потребностям.
Мне нужен тот, который также снизит индексы, которые будут создавать резервные копии уникальных или первичных ограничений (кроме случаев, когда они не могут быть удалены при резервном копировании внешнего ключа)
DECLARE @SqlScript NVARCHAR(MAX);
SELECT @SqlScript =
(
SELECT
'
BEGIN TRY
'+ CASE WHEN 1 IN (i.is_primary_key, i.is_unique_constraint) THEN
'
ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + QUOTENAME(i.name) + ';'
else
'
DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(t.name)
END+'
END TRY
BEGIN CATCH
RAISERROR(''Could not drop %s on table %s'', 0,1, ' + QUOTENAME(i.name, '''') + ', ' + QUOTENAME(t.name, '''') + ')
END CATCH
'
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc IN ('CLUSTERED', 'NONCLUSTERED' )
ORDER BY t.object_id, i.index_id DESC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)');
--Return script that will be run
SELECT @SqlScript AS [processing-instruction(x)]
FOR XML PATH('');
EXEC (@SqlScript);