在SQL Server数据库中删除索引

Question

如何在SQL Server 2008数据库中删除索引？以下是我所拥有的内容：

declare @procname varchar(500) 
declare cur cursor 
for
select  name from sysindexes 
open cur
fetch next from cur into @procname
while @@FETCH_STATUS=0
begin
 exec ('drop index ' + @procname)
 fetch next from cur into @procname
  end
 close cur
 deallocate cur

- mohammed nadir

Answer 1

你需要使用类似这样的语法，因为DROP INDEX语句要求你指定表名：

-- define variables for index, schema and table name
DECLARE @indexname sysname
DECLARE @schemaname sysname
DECLARE @tablename sysname

-- declare variable for actual DROP statement
DECLARE @dropstatement NVARCHAR(1000)

-- declare cursor for iterating over all indexes    
DECLARE index_cursor CURSOR LOCAL FAST_FORWARD
FOR
    SELECT ix.name, t.name, s.name
    FROM sys.indexes ix
    INNER JOIN sys.tables t ON t.object_id = ix.object_id
    INNER JOIN sys.schema s ON t.schema_id = s.schema_id
    WHERE t.is_ms_shipped = 0

-- open cursor     
OPEN index_cursor

-- get first index, table and schema name
FETCH NEXT FROM index_cursor INTO @indexname, @tablename, @schemaname

WHILE @@FETCH_STATUS = 0
BEGIN
    -- define the DROP statement
    SET @dropstatement = N'DROP INDEX ' + QUOTENAME(@indexname) + 
                         N' ON ' QUOTENAME(@schemaname) + N'.' + 
                         QUOTENAME(@tablename)

    -- execute the DROP statement        
    EXEC sp_executesql @dropstatement

    -- get next index, table and schema name   
    FETCH NEXT FROM index_cursor INTO @indexname, @tablename, @schemaname    
END

CLOSE index_cursor
DEALLOCATE index_cursor

- marc_s

---

我认为应该是 EXEC sp_executesql @dropstatement，而不是 EXEC sp_execute。此外，最好将模式包含在表名中，并使用 QUOTENAME 确保命名不良的对象不会导致错误。 - GarethD

@GarethD：当然 - 谢谢！现在这真的是非常安全和明确的！ :-) - marc_s