이전 포스트에서 idxinfo라 명명한 인덱스 정보를 조회하는 뷰는 소개한 바 있다.
이번 포스트에서는 SQL Server 2005 이상에서 sys.objects 및 sys.indexes Object Catalog View를 이용하여 뷰를 재구성해 보았다.
IF EXISTS (SELECT [name]
FROM sys.sysobjects
WHERE [type] = 'V'
AND [name] = 'idxinfo')
BEGINDROP VIEW [dbo].[idxinfo]
ENDGO CREATE VIEW [dbo].[idxinfo]
ASSELECT o.[schema_id] AS [schema_id]
, schema_name(o.[schema_id]) AS [schema_name]
, o.[object_id] AS [object_id]
, o.[name] AS [table_name]
, i.[name] AS [index_name]
, i.[type_desc] AS [index_type]
, CASE i.[is_unique] WHEN 1 THEN 'Unique'
WHEN 0 THEN 'Non-unique'
ELSE '' END AS [uniqueness]
, CASE WHEN i.[is_primary_key] = 1 THEN 'PK'
WHEN i.[is_unique_constraint] = 1 THEN 'UK'
ELSE '' END AS [constraint]
, CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 1) IS NULL
THEN ''
ELSE index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 1) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 2) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 2) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 3) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 3) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 4) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 4) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 5) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 5) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 6) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 6) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 7) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 7) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 8) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 8) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 9) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 9) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 10) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 10) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 11) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 11) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 12) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 12) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 13) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 13) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 14) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 14) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 15) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 15) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 16) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 16)
END AS [index_columns]
, i.[fill_factor] AS [fillfator]
, CASE i.[is_disabled] WHEN 0 THEN 'Active'
WHEN 1 THEN 'Inactive' END AS [is_disabled]
FROM sys.objects o WITH(NOLOCK)
LEFT OUTER JOIN
sys.indexes i WITH(NOLOCK)
ON o.[object_id] = i.[object_id]
WHERE o.[type] = 'U'
AND i.[index_id] < 255
GO뷰의 조회방법은 동일하다.
실행 예1 전체 인덱스 정보 조회
SELECT *
FROM dbo.idxinfo
GO
실행 예2 Heap 구조의(Clustered Index가 없는) 테이블 조회
SELECT *
FROM dbo.idxinfo
WHERE ClusterType = 'HEAP'
GO
실행 예3 특정 테이블의 인덱스 조회
SELECT *
FROM idxinfo
WHERE [object_id] = object_id('schema_name.table_name')
GO --or
SELECT *
FROM idxinfo
WHERE [schema_name] = 'schema_name'
AND [table_name] = 'table_name'
GO
아래의 뷰 는 sys.dm_db_index_physical_stats Dynamic Management Function을 사용하여 인덱스의 물리적 통계정보를 함께 조회한다.
IF EXISTS (SELECT [name]
FROM sys.sysobjects
WHERE [type] = 'V'
AND [name] = 'idxinfo2')
BEGINDROP VIEW [dbo].[idxinfo2]
ENDGO CREATE VIEW [dbo].[idxinfo2]
ASSELECT o.[schema_id] AS [schema_id]
, schema_name(o.[schema_id]) AS [schema_name]
, o.[object_id] AS [object_id]
, o.[name] AS [table_name]
, i.[name] AS [index_name]
, i.[type_desc] AS [index_type]
, CASE i.[is_unique] WHEN 1 THEN 'Unique'
WHEN 0 THEN 'Non-unique'
ELSE '' END AS [uniqueness]
, CASE WHEN i.[is_primary_key] = 1 THEN 'PK'
WHEN i.[is_unique_constraint] = 1 THEN 'UK'
ELSE '' END AS [constraint]
, CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 1) IS NULL
THEN ''
ELSE index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 1) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 2) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 2) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 3) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 3) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 4) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 4) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 5) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 5) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 6) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 6) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 7) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 7) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 8) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 8) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 9) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 9) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 10) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 10) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 11) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 11) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 12) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 12) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 13) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 13) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 14) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 14) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 15) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 15) END
+ CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 16) IS NULL
THEN ''
ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 16)
END AS [index_columns]
, i.[fill_factor] AS [fillfator]
, CASE i.[is_disabled] WHEN 0 THEN 'Active'
WHEN 1 THEN 'Inactive' END AS [is_disabled]
, [st].[index_depth] AS [index_depth]
, [st].[index_level] AS [index_level]
, [st].[page_count] AS [page_count]
, ROUND([st].[avg_fragmentation_in_percent], 2) AS [avg_fragmentation_in_percent]
FROM sys.objects o WITH(NOLOCK)
LEFT OUTER JOIN
sys.indexes i WITH(NOLOCK)
ON o.[object_id] = i.[object_id]
LEFT JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , 'SAMPLED') st
ON [i].[object_id] = [st].[object_id]
AND [i].[index_id] = [st].[index_id]
WHERE o.[type] = 'U'
AND i.[index_id] < 255
GO'연구개발 > SQL2005' 카테고리의 다른 글
| SQL Server 2008 설치시 Visual studio tools for applications 2.0 관련 에러를 해결하는 방법 (0) | 2011.07.18 |
|---|---|
| SSMS에서의 System.Security.Cryptography.CryptographicException 에러 (0) | 2011.07.18 |
| MS SQL Server 2005 설치 후 기본적으로 해야 할 일 (0) | 2011.07.18 |
| MySQL의 INET_ATON()함수와 ATON_INET()함수 구현하기 (0) | 2011.07.18 |
| Oracle의 LPAD함수의 기능 구현하기 (0) | 2011.07.18 |