MSSQL : 특정 테이블 TABLE 에 할당된 모든 인덱스 INDEX 를 찾아 쿼리로 추출

 

 

I want to extract all indexes assigned to a specific table through a query. I want to extract it and use it elsewhere.

 

특정 테이블에 할당된 모든 인덱스를 찾아 쿼리로 추출하고자 한다.

 

DECLARE @TableName NVARCHAR(255) = 'UNIV_PROJECT_Unit'; -- 여기 테이블명 수정해서 사용해라.
DECLARE @IndexScript NVARCHAR(MAX) = '';

SELECT 
    @IndexScript = @IndexScript +
        'CREATE ' + 
        CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END +
        i.type_desc + ' INDEX ' + 
        QUOTENAME(i.name) + ' ON ' + 
        QUOTENAME(OBJECT_NAME(i.object_id)) + '(' + 
        STUFF((SELECT ', ' + QUOTENAME(c.name)
               FROM sys.index_columns AS ic
               INNER JOIN sys.columns AS c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
               WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id
               ORDER BY ic.key_ordinal
               FOR XML PATH('')), 1, 2, '') + ');' + CHAR(13) + CHAR(10)
FROM sys.indexes AS i
WHERE OBJECT_NAME(i.object_id) = @TableName;

PRINT @IndexScript;

 

 

 

결과물

 

CREATE UNIQUE CLUSTERED INDEX [PK_UNIV_PROJECT_Unit_1] ON [UNIV_PROJECT_Unit]([CompanyID], [UnitID]);
CREATE NONCLUSTERED INDEX [idx_unitCategoryID] ON [UNIV_PROJECT_Unit]([UnitCategoryID]);
CREATE NONCLUSTERED INDEX [IX_UNIV_PROJECT_Unit] ON [UNIV_PROJECT_Unit]([CompanyID], [GroupID], [GroupVersion], [IsUse]);