学步园

 

  批量导出索引：

 

           SELECT   'CREATE  INDEX  ' + [name] 
                    + ' ON [' + OBJECT_NAME(object_id) + '] ('
                    + REVERSE(SUBSTRING(REVERSE(( SELECT    name
                                                            + CASE WHEN sc.is_descending_key = 1 THEN ' DESC'
                                                                   ELSE ' ASC'
                                                              END + ','
                                                  FROM      sys.index_columns sc
                                                            JOIN sys.columns c ON sc.object_id = c.object_id
                                                                                  AND sc.column_id = c.column_id
                                                  WHERE     OBJECT_NAME(sc.object_id) IN (SELECT sysno from IC_History.dbo.temp)
                                                            AND sc.object_id = i.object_id
                                                            AND sc.index_id = i.index_id
                                                  ORDER BY  index_column_id ASC
                                                FOR
                                                  XML PATH('')
                                                )), 2, 8000)) + ') With (Online=On)'
           FROM     sys.indexes i
           WHERE    OBJECT_NAME(object_id) IN  ('表名' )
                    AND CASE WHEN 1 = 1
                                  AND is_primary_key = 1
                                  AND type = 1 THEN 0
                             ELSE 1
                        END = 1
                   -- AND is_unique_constraint = 0
                   -- AND is_primary_key = 0