用sql 来生成索引

BEGIN
         WITH  tx  AS
        (
                 SELECT  a. object_id
                      ,b.name  AS  schema_name
                      ,a.name  AS  table_name
                      ,c.name  as  ix_name
                      ,c.is_unique  AS  ix_unique
                      ,c.type_desc  AS  ix_type_desc
                      ,d.index_column_id
                      ,d.is_included_column
                      ,e.name  AS  column_name
                      ,f.name  AS  fg_name
                      ,d.is_descending_key  AS  is_descending_key
                      ,c.is_primary_key
                      ,c.is_unique_constraint
                   FROM  sys.tables  AS  a
                  INNER   JOIN  sys.schemas  AS  b             ON  a.schema_id  =  b.schema_id  AND  a.is_ms_shipped  =   0
                  INNER   JOIN  sys.indexes  AS  c             ON  a. object_id   =  c. object_id
                  INNER   JOIN  sys.index_columns  AS  d       ON  d. object_id   =  c. object_id   AND  d.index_id  =  c.index_id
                  INNER   JOIN  sys.columns  AS  e             ON  e. object_id   =  d. object_id   AND  e.column_id  =  d.column_id
                  INNER   JOIN  sys.data_spaces  AS  f         ON  f.data_space_id  =  c.data_space_id
        )
         SELECT
               Drop_Index    =   CASE   WHEN  (a.is_primary_key  =   1   OR  a.is_unique_constraint  =   1 )
                                    THEN   ' ALTER TABLE  '   +  a.table_name  +   '  DROP CONSTRAINT  '   +  a.ix_name
                                    ELSE   ' DROP INDEX  '   +  a.ix_name collate SQL_Latin1_General_CP1_CI_AS  +   '  ON  '   +  a.schema_name  +   ' . '   +  a.table_name   END
              ,Create_Index  =   CASE   WHEN  (a.is_primary_key  =   1   OR  a.is_unique_constraint  =   1 )
                                    THEN   ' ALTER TABLE  '   +  a.table_name  +   '  ADD CONSTRAINT  '   +  a.ix_name 
                                        +   CASE   WHEN  a.is_primary_key  =   1   THEN   '  PRIMARY KEY '   ELSE   '  UNIQUE '   END   +   ' ( '   +  indexColumns.ix_index_column_name  +   ' ) '
                                    ELSE   ' CREATE  '   +   CASE   WHEN  a.ix_unique  =   1   THEN   ' UNIQUE  '   ELSE   ''   END  
                                        +  a.ix_type_desc  +   '  INDEX  '   +  a.ix_name collate SQL_Latin1_General_CP1_CI_AS  +   '  ON  '   +  a.schema_name
                                        +   ' . '   +  a.table_name  +   ' ( '   +  indexColumns.ix_index_column_name  +   ' ) '
                                        +   CASE   WHEN  IncludeIndex.ix_included_column_name  IS   NOT   NULL   THEN   '  INCLUDE ( '   +  IncludeIndex.ix_included_column_name  +   ' ) '   ELSE   ''   END
                                        +   '  ON [ '   +  a.fg_name  + ' ] '   END
              , CASE   WHEN  a.ix_unique  =   1   THEN   ' UNIQUE '   END   AS  ix_unique
              ,a.ix_type_desc
              ,a.ix_name
              ,a.schema_name
              ,a.table_name
              ,indexColumns.ix_index_column_name
              ,IncludeIndex.ix_included_column_name
              ,a.fg_name
              ,a.is_primary_key
              ,a.is_unique_constraint                                       
         FROM
        (
                 SELECT   DISTINCT
                       ix_unique
                      ,ix_type_desc
                      ,ix_name
                      ,schema_name
                      ,table_name
                      ,fg_name
                      ,is_primary_key
                      ,is_unique_constraint
                   FROM  tx
        )  AS  a
         OUTER  APPLY
        (
                 SELECT  ix_index_column_name
                        =   STUFF ((
                                 SELECT   ' , '   +  column_name  +   CASE   WHEN  is_descending_key  =   1   THEN   '  DESC '   ELSE   ''   END
                                   FROM  tx  AS  b
                                  WHERE  schema_name  =  a.schema_name
                                    AND  table_name = a.table_name
                                    AND  ix_name = a.ix_name
                                    AND  ix_type_desc = a.ix_type_desc
                                    AND  fg_name = a.fg_name
                                    AND  is_included_column = 0
                                  ORDER   BY  index_column_id
                                    FOR  XML PATH( '' )
                                ), 1 , 1 , '' )
        )IndexColumns
         OUTER  APPLY
        (
                 SELECT  ix_included_column_name
                        =   STUFF ((
                                 SELECT   ' , '   +  column_name
                                   FROM  tx  AS  b
                                  WHERE  schema_name  =  a.schema_name
                                    AND  table_name = a.table_name
                                    AND  ix_name = a.ix_name
                                    AND  ix_type_desc = a.ix_type_desc
                                    AND  fg_name = a.fg_name
                                    AND  is_included_column = 1
                                  ORDER   BY  index_column_id
                                    FOR  XML PATH( '' )
                                ),  1 , 1 , '' )
        )IncludeIndex
         ORDER   BY  a.schema_name,a.table_name,a.ix_name;
END