問題描述

那么，如何使用一個命令刪除 SQL 數(shù)據(jù)庫中的所有索引?我有這個命令可以獲取所有 20 個左右的 drop 語句，但是如何從這個結(jié)果集"運行所有這些 drop 語句?

So, how can I drop all indexes in a SQL database with one command? I have this command that will get me all the 20 or so drop statements, but how can I run all of those drop statements from this "result set"?

select * from vw_drop_idnex;

另一個給我相同列表的變體是:

Another variation that gives me the same list is:

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_%'

我嘗試執(zhí)行exec(select cmd from vw_drop_idnex)"，但沒有成功.我正在尋找像 for 循環(huán)一樣工作的東西，并逐個運行查詢.

I tried to do "exec(select cmd from vw_drop_idnex)" and it didn't work. I am looking for something that works like a for loop and runs the queries one by one.

在 Rob Farleys 的幫助下，劇本的最終草稿是:

With Rob Farleys help, final draft of the script is:

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

推薦答案

你們很親近.

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

這篇關(guān)于如何使用一個命令刪除 SQL 數(shù)據(jù)庫中的所有索引?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！