Как я могу отбросить все индексы в базе данных 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);