반응형
/*
MSSQL 공통버전
*/
-- 인덱스 정보 조회
IF EXISTS (SELECT [name]
FROM sys.sysobjects
WHERE [type] = 'V' AND [name] = 'idxinfo')
BEGIN
DROP VIEW [dbo].[idxinfo]
END
GO
CREATE VIEW dbo.idxinfo
AS
SELECT 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, 10) 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, 11) 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, 12) 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, 13) 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, 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
--전체 인덱스 정보 조회
SELECT * FROM dbo.idxInfo
GO
--Heap 구조의(Clustered Index가 없는) 테이블 조회
SELECT * FROM dbo.idxInfo
WHERE ClusterType = 'HEAP'
GO
--AdventureWorks 의 dbo.Employee 테이블 인덱스 조회
SELECT * FROM dbo.idxInfo
WHERE Owner = 'head1ton'
AND TableName = 'Employee'
GO
/*
MSSQL 2005 이상 버전
*/
--인덱스 정보 조회
IF EXISTS (SELECT [name]
FROM sys.sysobjects
WHERE [type] = 'V'
AND [name] = 'idxInfo')
BEGIN
DROP VIEW [dbo].[idxInfo]
END
GO
ALTER VIEW [dbo].[idxinfo]
AS
SELECT 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 [fillfactor]
, 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
-- 전체 인덱스 정보 조회
SELECT * FROM dbo.idxInfo;
GO
-- Heap구조의 (CLUSTERED INDEX가 없는) 테이블 조회
SELECT *
FROM dbo.idxInfo
WHERE index_type = 'HEAP'
GO
-- 특정 테이블의 인덱스 조회
SELECT *
FROM dbo.idxInfo
WHERE [OBJECT_ID] = OBJECT_ID('schema_name.table_name')
GO
--or
SELECT *
FROM dbo.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')
BEGIN
DROP VIEW [dbo.[idxinfo2]
END
GO
CREATE VIEW [dbo].[idxinfo2]
AS
SELECT 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 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 [fillfactor]
, 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
-- 전체 인덱스 정보 조회
SELECT * FROM dbo.idxInfo2;
GO
-- Heap구조의 (CLUSTERED INDEX가 없는) 테이블 조회
SELECT *
FROM dbo.idxInfo2
WHERE index_type = 'HEAP'
GO
-- 특정 테이블의 인덱스 조회
SELECT *
FROM dbo.idxInfo2
WHERE [OBJECT_ID] = OBJECT_ID('schema_name.table_name')
GO
--or
SELECT *
FROM dbo.idxInfo
WHERE [SCHEMA_NAME] = 'schema_name'
AND [TABLE_NAME] = 'table_name'
GO
반응형
'연구개발 > SQL2008' 카테고리의 다른 글
데이터베이스 복원 예시 (오프라인) (0) | 2010.04.29 |
---|---|
데이터베이스 백업 / 복구모델 (0) | 2010.04.27 |
FullText Search 시에 가중치 주기 (0) | 2010.04.22 |
커서 cursor (0) | 2010.04.20 |
DatePart 주차 (0) | 2010.04.19 |