• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Dropping ALL indexes (incl PK) on a table

is there a way to drop all indexes (PK + indexes) on a table? is there a stored procedures? i don't know the names of the indexes... i want to use it in my program to clear a table of the indexes and rebuild new indexes...
0
pvsadm
Asked:
pvsadm
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
In summary:

*) capture current info using EXEC sp_helpindex 'tablename'
*) drop non-clustered indexes
*) drop clustered index (if any)
*) recreate non-clustered indexes
*) recreate clustered index (if any)



0
 
Scott PletcherSenior DBACommented:
In more detail:
Here's code that SHOULD be able to drop all indexes (uncomment the EXECs when you are SURE you're ready to):


IF OBJECT_ID('tempdb..#indexes') IS NOT NULL
     DROP TABLE #indexes
CREATE TABLE #indexes (name SYSNAME, description VARCHAR(210), keys NVARCHAR(2078))

DECLARE @tableName VARCHAR(40)
SET @tableName = 'tbOrsEmp'
--EXEC sp_helpindex @tableName

SET NOCOUNT ON
INSERT INTO #indexes
EXEC sp_helpindex @tableName

DECLARE indexesCsr CURSOR FOR
SELECT name, description, keys
FROM #indexes
FOR READ ONLY

DECLARE @name CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description VARCHAR(210)
DECLARE @keys VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)
DECLARE @name_clus CHAR(64)  --if you need NVARCHAR, use SYSNAME as datatype
DECLARE @description_clus VARCHAR(210)
DECLARE @keys_clus VARCHAR(1039)  --if you need NVARCHAR, use NVARCHAR(2078)

--drop existing indexes, non-clustered first, then clustered, if any
OPEN indexesCsr
FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
WHILE @@FETCH_STATUS = 0
BEGIN
     IF LEFT(@description, 9) = 'clustered'
     BEGIN
          SET @name_clus = @name
          SET @description_clus = @description
          SET @keys_clus = @keys
     END --IF
     ELSE
     BEGIN
          PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name
          --EXEC('DROP INDEX ' + @name)
     END --ELSE
     FETCH NEXT FROM indexesCsr INTO @name, @description, @keys
END --WHILE
CLOSE indexesCsr
DEALLOCATE indexesCsr
IF @name_clus IS NOT NULL
BEGIN
     PRINT 'DROP INDEX ' + RTRIM(@tableName) + '.' + @name_clus
     --EXEC('DROP INDEX ' + @name)
END --IF
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now