데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기

데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기

 

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

데이터베이스에 생성되어 있는 모든 인덱스를 생성하거나 삭제하는 스크립트를 만들어 본다. 이렇게 스크립트를 만들어 놓는 이유는 DBA로서 항상 복구할 준비를 할 수 있어야 하기 때문이다.

 

인덱스가 삭제되거나 손상되었을 때 또는 사본을 만들어야 할 때 유용하게 사용할 수 있다.

 

[인덱스 삭제 스크립트]

데이터베이스에 있는 모든 인덱스를 삭제하는 스크립트를 생성한다.

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

 

 

 

[인덱스 생성 스크립트]

데이터베이스의 모든 정규 인덱스를 생성한다. Sys.tables, sys.indexes, sys.index_column, sys.colimns 뷰를 사용하여 메타 데이터를 쿼리 한다. 생성된 스크립트는 다음과 같은 특징이 있다.

• 인덱스가 비활성과 된 경우 비활성화된 코드를 생성한다.
• Include column 경우 동일한 순서로 포함된다.
• 인덱스의 모든 속성(ALLOW_PAGE_LOCKS, STATISTICS_NORECOMPUTE, FILLFACTOR, SORT_IN_TEMPDB 등)을 포함한다.
• 생성된 스크립트는 기본키에 연결하지는 않는다.
• 파티션된 인덱스는 스크립트에 포함되지 않는다.

 

declare @SchemaName varchar(100)declare @TableName varchar(256) declare @IndexName varchar(256) declare @ColumnName varchar(100) declare @is_unique varchar(100) declare @IndexTypeDesc varchar(100) declare @FileGroupName varchar(100) declare @is_disabled varchar(100) declare @IndexOptions varchar(max) declare @IndexColumnId int declare @IsDescendingKey int declare @IsIncludedColumn int declare @TSQLScripCreationIndex varchar(max) declare @TSQLScripDisableIndex varchar(max)   declare CursorIndex cursor for select schema_name(t.schema_id) [schema_name], t.name, ix.name, case when ix.is_unique = 1 then 'UNIQUE ' else '' END , ix.type_desc, case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end + case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end + case when ix.allow_row_locks=1 then 'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end + case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end + case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) AS IndexOptions , ix.is_disabled , FILEGROUP_NAME(ix.data_space_id) FileGroupName from sys.tables t inner join sys.indexes ix on t.object_id=ix.object_id where ix.type>0 --and ix.is_primary_key=0 --and ix.is_unique_constraint=0 --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName and t.is_ms_shipped=0 and t.name<>'sysdiagrams' order by schema_name(t.schema_id), t.name, ix.name   open CursorIndex fetch next from CursorIndex into @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName   while (@@fetch_status=0) begin declare @IndexColumns varchar(max) declare @IncludedColumns varchar(max)   set @IndexColumns='' set @IncludedColumns=''   declare CursorIndexColumn cursor for select col.name, ixc.is_descending_key, ixc.is_included_column from sys.tables tb inner join sys.indexes ix on tb.object_id=ix.object_id inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id inner join sys.columns col on ixc.object_id =col.object_id and ixc.column_id=col.column_id where ix.type>0 and (ix.is_primary_key=0 or ix.is_unique_constraint=0) and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName order by ixc.index_column_id   open CursorIndexColumn fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn   while (@@fetch_status=0) begin if @IsIncludedColumn=0 set @IndexColumns=@IndexColumns + @ColumnName + case when @IsDescendingKey=1 then ' DESC, ' else ' ASC, ' end else set @IncludedColumns=@IncludedColumns + @ColumnName +', '   fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn end   close CursorIndexColumn deallocate CursorIndexColumn   set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1) set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end -- print @IndexColumns -- print @IncludedColumns   set @TSQLScripCreationIndex ='' set @TSQLScripDisableIndex ='' set @TSQLScripCreationIndex='CREATE '+ @is_unique +@IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ '('+@IndexColumns+') '+ case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'   if @is_disabled=1 set @TSQLScripDisableIndex= CHAR(13) +'ALTER INDEX ' +QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13)   print @TSQLScripCreationIndex print @TSQLScripDisableIndex   fetch next from CursorIndex into @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName   end close CursorIndex deallocate CursorIndex

 

 

위 스크립트를 사용하기 전에 항상 테스트 환경에서 영향 평가를 할 수 있도록 한다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/

 

 

2014-12-23 / 강성욱 / http://sqlmvp.kr

 

 

Mssql, sqlserver, create index, drop index, DBA, 인덱스 생성, 인덱스 삭제, 인덱스 관리, 데이터베이스관리, 스크립트 생성