Created
September 29, 2020 06:31
-
-
Save chenbojian/4f5148ad2c627704baa30752e07b33e1 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--https://dba.stackexchange.com/questions/20355/generate-create-script-for-all-indexes | |
DECLARE @SchemaName VARCHAR(256)DECLARE @TableName VARCHAR(256) | |
DECLARE @IndexName VARCHAR(256) | |
DECLARE @TSQLDropIndex VARCHAR(MAX) | |
DECLARE CursorIndexes CURSOR FOR | |
SELECT schema_name(t.schema_id), t.name, i.name | |
FROM sys.indexes i | |
INNER JOIN sys.tables t ON t.object_id= i.object_id | |
WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams' | |
and (is_primary_key=0 and is_unique_constraint=0) | |
OPEN CursorIndexes | |
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName | |
WHILE @@fetch_status = 0 | |
BEGIN | |
SET @TSQLDropIndex = 'DROP INDEX '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + '.' +QUOTENAME(@IndexName) | |
PRINT @TSQLDropIndex | |
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName | |
END | |
CLOSE CursorIndexes | |
DEALLOCATE CursorIndexes |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment