T-SQL::Index Script

현재 존재하는 Index 스크립트 생성.

T-SQL::List all indexes in database  에 관련 함수, view 생성해야함.

 

-- INDEX Script
SELECT  
    CASE WHEN T.TABLE_NAME IS NULL THEN 
        'CREATE ' 
        + CASE IS_UNIQUE WHEN 1 THEN ' UNIQUE' ELSE '' END 
        + CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END 
        + ' INDEX [' + INDEX_NAME + '] ON [' + v.TABLE_NAME + ']' 
        + ' (' + COLUMN_LIST + ') ON ' + FILE_GROUP 
    ELSE 
        'ALTER TABLE ['+T.TABLE_NAME+']' 
        +' ADD CONSTRAINT ['+INDEX_NAME+']' 
        +' PRIMARY KEY ' 
        + CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END 
        + ' (' + COLUMN_LIST + ')' 
    END 
FROM 
    dbo.vAllIndexes v 
LEFT OUTER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T  
ON 
    T.CONSTRAINT_NAME = v.INDEX_NAME 
    AND T.TABLE_NAME = v.TABLE_NAME  
    AND T.CONSTRAINT_TYPE = 'PRIMARY KEY' 
ORDER BY 
    v.TABLE_NAME, 
    IS_CLUSTERED DESC