用sql 来生成索引

红太狼 2022-06-12 02:15 138阅读 0赞

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