SQL Server Index Generate Script (All Tables)

Aşağıda ki script ile Veritabanı üzerinde yer alan Index lerinizin Create scriptini üreterek yedekleyebilir veya geçişleriniz de kullanabilirsiniz.

SELECT
  ' CREATE ' +
  CASE
    WHEN I.is_unique = 1 THEN ' UNIQUE '
    ELSE ''
  END +
  I.type_desc COLLATE database_default + ' INDEX ' +
  I.name + ' ON ' +
  SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
  KeyColumns + ' )  ' +
  ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
  ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
  CASE
    WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
    ELSE ' PAD_INDEX = OFF '
  END + ',' +
  'FILLFACTOR = ' + CONVERT(CHAR(5), CASE
    WHEN I.fill_factor = 0 THEN 100
    ELSE I.fill_factor
  END) + ',' +
  -- default value  
  'SORT_IN_TEMPDB = OFF ' + ',' +
  CASE
    WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
    ELSE ' IGNORE_DUP_KEY = OFF '
  END + ',' +
  CASE
    WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
    ELSE ' STATISTICS_NORECOMPUTE = ON '
  END + ',' +
  -- default value   
  ' DROP_EXISTING = ON ' + ',' +
  -- default value   
  ' ONLINE = OFF ' + ',' +
  CASE
    WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
    ELSE ' ALLOW_ROW_LOCKS = OFF '
  END + ',' +
  CASE
    WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
    ELSE ' ALLOW_PAGE_LOCKS = OFF '
  END + ' ) ON [' +
  DS.name + ' ] ' [CreateIndexScript]
FROM sys.indexes I
JOIN sys.tables T
  ON T.object_id = I.object_id
JOIN sys.sysindexes SI
  ON I.object_id = SI.Id
    AND I.index_id = SI.indid
JOIN (SELECT
    *
  FROM (SELECT
      IC2.object_id
       ,IC2.index_id
       ,STUFF((SELECT
          ' , ' + C.name +
          CASE
            WHEN MAX(CONVERT(INT, IC1.is_descending_key)) = 1 THEN ' DESC '
            ELSE ' ASC '
          END
        FROM sys.index_columns IC1
        JOIN sys.columns C
          ON C.object_id = IC1.object_id
          AND C.column_id = IC1.column_id
          AND IC1.is_included_column = 0
        WHERE IC1.object_id = IC2.object_id
        AND IC1.index_id = IC2.index_id
        GROUP BY IC1.object_id
            ,C.name
            ,index_id
        ORDER BY MAX(IC1.key_ordinal)
        FOR XML PATH (''))
      , 1, 2, '') KeyColumns
    FROM sys.index_columns IC2
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables   
    GROUP BY IC2.object_id
        ,IC2.index_id) tmp3) tmp4
  ON I.object_id = tmp4.object_id
    AND I.index_id = tmp4.index_id
JOIN sys.stats ST
  ON ST.object_id = I.object_id
    AND ST.stats_id = I.index_id
JOIN sys.data_spaces DS
  ON I.data_space_id = DS.data_space_id
JOIN sys.filegroups FG
  ON I.data_space_id = FG.data_space_id
LEFT JOIN (SELECT
    *
  FROM (SELECT
      IC2.object_id
       ,IC2.index_id
       ,STUFF((SELECT
          ' , ' + C.name
        FROM sys.index_columns IC1
        JOIN sys.columns C
          ON C.object_id = IC1.object_id
          AND C.column_id = IC1.column_id
          AND IC1.is_included_column = 1
        WHERE IC1.object_id = IC2.object_id
        AND IC1.index_id = IC2.index_id
        GROUP BY IC1.object_id
            ,C.name
            ,index_id
        FOR XML PATH (''))
      , 1, 2, '') IncludedColumns
    FROM sys.index_columns IC2
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables    
    GROUP BY IC2.object_id
        ,IC2.index_id) tmp1
  WHERE IncludedColumns IS NOT NULL) tmp2
  ON tmp2.object_id = I.object_id
    AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0
AND I.is_unique_constraint = 0  
 

--AND I.Object_id = object_id('Person.Address') -- for all tables 
--AND I.name = 'IX_Address_PostalCode' -- for all indexes

 


Leave a Reply

Your email address will not be published. Required fields are marked *