?
Solved

Dropping ALL indexes (incl PK) on a table

Posted on 2003-02-20
2
Medium Priority
?
305 Views
Last Modified: 2008-03-06
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
Comment
Question by:pvsadm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7989531
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 7989631
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question