반응형

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

 

  • 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, 인덱스 생성, 인덱스 삭제, 인덱스 관리, 데이터베이스관리, 스크립트 생성

반응형

+ Recent posts