T-SQL::List all indexes in database

데이터 베이스에 있는 모든 인덱스 정보를 보기 위함.

sys.indexes, sys.index_columns, sys.columns  정보를 보면 복합 인덱스 일경우 row가 한개 이상으로 나타나서 한눈에 보기가 힘들다.

select  t.name as table_name ,ind.name as index_name, 
        ind.index_id, ic.index_column_id, col.name as column_name 
from    sys.indexes ind
inner join    sys.index_columns ic on ind.object_id = ic.object_id and ind.index_id = ic.index_id
inner join    sys.columns col on  ic.object_id = col.object_id and ic.column_id = col.column_id 
inner join    sys.tables t on  ind.object_id = t.object_id
where   ind.is_primary_key = 0   
    and ind.is_unique = 0     
    and ind.is_unique_constraint = 0    
    and t.is_ms_shipped = 0
order by    t.name, ind.name, ind.index_id, ic.index_column_id
 

그래서 다음과 같이 처리한다.

1. 함수 생성 :: 컬럼의 인덱스가 내림차순인지 오름차순인지 확인

CREATE FUNCTION dbo.GetIndexColumnOrder 
( 
    @object_id INT, 
    @index_id TINYINT, 
    @column_id TINYINT 
) 
RETURNS NVARCHAR(5) 
AS 
BEGIN 
    DECLARE @r NVARCHAR(5) 
    SELECT @r = CASE INDEXKEY_PROPERTY 
    ( 
        @object_id, 
        @index_id, 
        @column_id, 
        'IsDescending' 
    ) 
        WHEN 1 THEN N' DESC' 
        ELSE N'' 
    END 
    RETURN @r 
END ;

2. 인덱스의 모든 컬럼 정보

CREATE FUNCTION dbo.GetIndexColumns 
( 
    @table_name SYSNAME, 
    @object_id INT, 
    @index_id TINYINT 
) 
RETURNS NVARCHAR(4000) 
AS 
BEGIN 
    DECLARE 
        @colnames NVARCHAR(4000),  
        @thisColID INT, 
        @thisColName SYSNAME 
         
    SET @colnames = '[' + INDEX_COL(@table_name, @index_id, 1) + ']'  
        + dbo.GetIndexColumnOrder(@object_id, @index_id, 1) 
 
    SET @thisColID = 2 
    SET @thisColName = '[' + INDEX_COL(@table_name, @index_id, @thisColID) + '] '
        + dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)
 
    WHILE (@thisColName IS NOT NULL) 
    BEGIN 
        SET @thisColID = @thisColID + 1 
        SET @colnames = @colnames + ', ' + @thisColName 
 
        SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID) 
            + dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID) 
    END 
    RETURN @colNames 
END ;

 

3.인덱스 정보

CREATE VIEW dbo.V_ALLINDEXES 
AS
SELECT TABLE_NAME = OBJECT_NAME(i.id), 
      INDEX_NAME = i.name, 
      COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id, i.indid), 
      IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
      IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'), FILE_GROUP = g.GroupName 
FROM sysindexes i
INNER JOIN sysfilegroups g ON i.groupid = g.groupid
WHERE (i.indid BETWEEN 1 AND 254) -- leave out AUTO_STATISTICS: 
 AND (i.Status & 64)=0 -- leave out system tables: 
 AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0 ;