所以，我怎么能删除所有索引在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（选择vw_drop_idnex CMD）”，并没有奏效。 我在找的东西，就像一个for循环，并运行一个查询之一。
-----------------------
与Rob Farleys帮助，剧本的最终草案是：
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
这个工作对我来说，我们跳过SYS指标和约束
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
来源： 斯蒂芬·希尔的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
小的改进，以接受的答案，我不得不做我自己的情况，主要是考虑到模式：
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
另外：对我来说，它不能一气呵成，因为脚本比4000个字符运行。 我能想到的处理，唯一的办法是把一个“前20”在内部选择并执行它多次。
答案都不很适合我的需要。
我需要一个，也将删除索引备份唯一或主要的限制（除非这些不能被丢弃，因为它们备份外键）
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);
