Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gregoryagu
Yak Posting Veteran
80 Posts |
 Posted - 2008-07-31 : 17:29:14
|
| --A utility to Drop all indexes including primary keys on a table.--To Drop All Indexes on a Database, use the following line--Exec sp_MSforeachtable "Utils_DeleteAllIndexesOnTable '?'" --Note that to work, any Primary Key index must start with 'PK'--which is the MSSMS default.CREATE PROCEDURE [dbo].[Utils_DeleteAllIndexesOnTable] -- Add the parameters for the stored procedure here @TableName VarChar(100)ASBEGIN 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_cursorEND |
|
|
|
|
|
Drop All Indexes on a Table