데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

  • 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/

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
2010 재해복구를 위한 SQL Server 역할 가져오기 jevida(강성욱) 2017.01.11 2279
2009 비관리자 계정에 Profiler 실행 권한 부여하기 jevida(강성욱) 2017.01.11 3109
2008 SQL Server Agent 공유 일정 생성하기 jevida(강성욱) 2017.01.11 2146
2007 인덱스 리빌드는 통계를 업데이트 할까? jevida(강성욱) 2017.01.11 2317
2006 인덱스 유지관리 작업과 SQL Server 쿼리 성능 jevida(강성욱) 2017.01.11 3263
2005 네트워크 드라이브에 데이터베이스 복원하기 jevida(강성욱) 2017.01.11 4039
2004 확장 저장 프로시저를 활용한 논리디스크 용량 확인 jevida(강성욱) 2017.01.11 2442
2003 날짜 참조 테이블 만들기 jevida(강성욱) 2017.01.11 3021
2002 인덱스 상세 정보 확인 jevida(강성욱) 2017.01.11 3707
2001 DTC Transacntion 오버헤드 jevida(강성욱) 2017.01.11 1289
2000 대용량 로드를 위한 BULK INSERT 옵션 jevida(강성욱) 2017.01.11 5183
1999 SQL Server 2014 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1608
1998 SQL Server 2012 Contained Database jevida(강성욱) 2017.01.11 1000
1997 SQL Server 2008R2 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1420
1996 SQL Server 2005 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1355
1995 601 Error, Could not continue scan with NOLOCK due to SQL Server data Movement jevida(강성욱) 2017.01.11 3626
» 데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기 jevida(강성욱) 2017.01.11 1659
1993 SQL Server Spinlock 소개 jevida(강성욱) 2017.01.11 1584
1992 Ad-hoc 쿼리와 실행계획 jevida(강성욱) 2017.01.11 2569
1991 로그인 계정이 접근할 수 있는 데이터베이스 확인 jevida(강성욱) 2017.01.11 2986





XE Login