RSS

Drop All index in Table

17 Jun

To Drop ll index in Table by T-SQL i will Create SP to do this Task

CREATE PROCEDURE [dbo].[Utils_DeleteAllIndexesOnTable]
— Add the parameters for the stored procedure here
@TableName VarChar(100)
AS
BEGIN
Declare @IndexName varchar(100)
DECLARE index_cursor CURSOR FOR
SELECT name FROM sysindexes where id = object_id(@TableName)
AND NAME IS NOT NULL and ROWS > 0

OPEN index_cursor

— Perform the first fetch.
FETCH NEXT FROM index_cursor into @IndexName

WHILE @@FETCH_STATUS = 0
BEGIN

if left(@IndexName,2) = ‘PK’
BEGIN
print ‘drop constraint ‘ + @IndexName + ‘ on ‘ + @TableName
Exec( ‘ALTER TABLE ‘ + @TableName +
‘DROP CONSTRAINT ‘ + @IndexName )

END
ELSE
BEGIN
— This is executed as long as the previous fetch succeeds.
print ‘drop index ‘ + @IndexName + ‘ on ‘ + @TableName
Exec(‘drop index ‘ + @IndexName + ‘ on ‘ + @TableName)
END

FETCH NEXT FROM index_cursor into @IndexName

END

CLOSE index_cursor
DEALLOCATE index_cursor
END

Execute your Stored Procedure but don’t Forget to pass the Table name as parameter to SP

exec [Utils_DeleteAllIndexesOnTable] Your_Table_Name

 
Leave a comment

Posted by on June 17, 2013 in Script

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s