sql-server-2008 - 在 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

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