kr153
12/30/2016 - 9:08 AM

Delete all non clustered indexes - SQL Server

Delete all non clustered indexes - SQL Server

IF EXISTS
  (SELECT *
   FROM [tempdb].[dbo].sysobjects
   WHERE id = Object_id(N'[tempdb].[dbo].[##NonClusteredIndexesToDelete]') )
DROP TABLE ##nonclusteredindexestodelete
CREATE TABLE ##nonclusteredindexestodelete ([name] SYSNAME,
                                            [tablename] SYSNAME)
INSERT ##nonclusteredindexestodelete
SELECT sysindexes.[name],
       sysobjects.[name] AS TableName
FROM sysindexes(nolock)
INNER JOIN sysobjects ON sysindexes.id = sysobjects.id
WHERE (indid > 1 )-- delete only non clustered indexes

  AND (sysindexes.[name] NOT LIKE '_WA%' )-- don't delete statistics  
 
  AND (sysobjects.[type] != 'S' )-- don't delete system indexes

  AND (sysobjects.[xtype] != 'IT' )-- don't delete system indexes  
 
  AND (NOT (sysindexes.[status] = 4098
            AND sysindexes.indid = 2) )-- don't delete unique indexes
ORDER BY (sysindexes.[name]) DECLARE @name SYSNAME DECLARE @TableName SYSNAME DECLARE @sql VARCHAR(4096) DECLARE vcursor
CURSOR
FOR
SELECT [name],
       [tablename]
FROM ##nonclusteredindexestodelete
ORDER BY [name] OPEN vcursor FETCH NEXT
FROM vcursor INTO @name,
                  @TableName WHILE @@FETCH_STATUS = 0 BEGIN
SET @sql = 'drop index ' + @TableName + '.' + @name EXEC (@sql) FETCH NEXT
FROM vcursor INTO @name,
                  @TableName END CLOSE vcursor DEALLOCATE vcursor
DROP TABLE ##nonclusteredindexestodelete