Dropping indexes in SQL Server database

indexing, sql-server-2008

Solution

You need to use something like this, because the `DROP INDEX` statement requires you to specify the table name:

-- 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

Problem

How do you drop indexes in a SQL Server 2008 database? This is what I've got so far: ``` 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 ```

Original source