DEFINITIONS

Definitions More Info.
Definition ID208
TitleSQL
CategorySCRIPT
DefinitionDROP ALL NONCLUSTERED INDEXES SCRIPT
Definition Descriptionhttps://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/

T-SQL Script to Drop All SQL Server Indexes
Another related task is dropping all SQL Server indexes in a test environment then recreating them.
The following script allows you to generate a script to drop all regular non-unique indexes in a database:

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
RecordBycunay
Record Date02-07-2012 11:23:10
Düzenle
Kopyala
Sil