MS SQL Server에서 인덱스 정보를 조회하기 위해서는 Enterprise Manager(2000 버전 이전), SQL Server Managment Studio(2005 버전 이후)와 같은 GUI 관리 프로그램이나 sp_help 혹은 sp_helpindex와 같은 시스템 저장 프로시저를 사용해서 조회해야 한다. 하지만 이러한 툴들은 출력 형식이 정해져 있고 테이블 단위로 정보를 보여주기 때문에 보고서 용도로 사용하기에는 다소 불편함을 느낄수 있을 것이다. 내가 원하는 형식으로 인덱스 정보를 조회할 수 있는 방법이 없을까 고민하던 중 MS SQL Server에서 인덱스 정보를 조회하는 뷰를 작성하는 스크립트를 발견하였다. 블로그 주인장은 아래와 같이 원래의 스크립트를 수정하여 유용하게 사용하고 있다. 관심이 있는 분들은 각자의 구미에 맞게 스크립트를 수정하여 사용해 보길 바란다.
본 스크립트는 SQLLeader.com의 스크립트 자료실에서 얻은 스크립트를 수정한 것으로 작성자는 avigneau이다. 스크립트 테스트는 SQL Server 6.X, 7.0, 2000버전까지 이루어진 것 같다(참고로 주인장은 2005버전에 적용해 아무 이상 없이 사용하고 있다).
IF EXISTS (SELECT [name]
FROM sys.sysobjects
WHERE [type] = 'V'
AND [name] = 'idxinfo')
BEGINDROP VIEW [dbo].[idxinfo]
ENDGO CREATE VIEW dbo.idxinfo
ASSELECT o.id AS [TableId]
, u.name AS [Owner]
, o.name AS [TableName]
, i.indid AS [IndexID]
, CASE i.name WHEN o.name THEN '** NONE **'
ELSE i.name END AS [IndexName]
, CASE i.indid WHEN 1 THEN 'CLUSTERED'
WHEN 0 THEN 'HEAP'
ELSE 'NONCLUSTERED' END AS [ClusterType]
, CASE WHEN (i.status & 2048) > 0 THEN 'PRIMARY KEY'
WHEN (i.status & (2|4096)) > 0 THEN 'UNIQUE'
ELSE '' END AS [UniqueType]
, CASE WHEN (i.status & (2048)) > 0 OR ((i.status & (4096)) > 0 ) THEN 'CONSTRAINT'
WHEN i.indid = 0 THEN ' '
ELSE 'INDEX' END AS [IndexType]
, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1) IS NULL THEN ''
ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 1) END +
CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,2) END +
CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 3) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,3) END +
CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 4) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,4) END +
CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,5) END +
CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 6) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,6) END +
CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) END +
CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 8) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,8) END +
CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,9) END +
CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,10) END +
CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,11) END +
CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,12) END +
CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,13) END +
CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,14) END +
CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,15) END +
CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16) IS NULL THEN ''
ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,16) END AS [AllColName]
, OrigFillFactor AS [FillFactor]
FROM sysobjects o (NOLOCK)
LEFT OUTER JOIN sysindexes i (NOLOCK)
ON o.id = i.id
JOIN sysusers u (NOLOCK)
ON o.uid = u.uid
WHERE o.type = 'U' AND i.indid < 255
AND o.name NOT IN ('dtproperties')
AND i.name NOT LIKE '_WA_Sys_%' -- because of SQL Server 7.0
GO
실행 예1 전체 인덱스 정보 조회
SELECT *
FROM dbo.idxinfo
GO
실행 예2 Heap 구조의(Clustered Index가 없는) 테이블 조회
SELECT *
FROM dbo.idxinfo
WHERE ClusterType = 'HEAP'
GO
실행 예3 dbo.Employee 테이블의 인덱스 조회
SELECT *
FROM dbo.idxinfo
WHERE Owner = 'dbo'
AND TableName = 'Employee'
GO'연구개발 > SQL2005' 카테고리의 다른 글
| SQL Server Management Studio 및 Business Intelligence Development Studio 빨리 열기 (0) | 2011.07.18 |
|---|---|
| DDL 스트립트를 작성하는 프로그램 (오픈 소스) (0) | 2011.07.18 |
| [MS SQL Server New Feature 1] 최소 로깅 Insert (0) | 2011.07.18 |
| [MS SQL Server 2005] Windows 성능 로그 데이터와 추적의 상관 관계 지정 (0) | 2011.07.18 |
| 프로시져 연습 (0) | 2010.06.18 |