T-SQL::Index Script

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

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

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

-- 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