当前位置：Gxlcms > 数据库问题 > SQLServer批量生成某数据库中的所有索引的创建脚本

SQLServer批量生成某数据库中的所有索引的创建脚本

时间：2021-07-01 10:21:17 帮助过：7人阅读

exec sp_autoIdx ‘1’ ----直接生成索引 or exec sp_autoIdx ‘0’ @ifexec决定是否直接exec(@sqlon)在当前数据库生成索引。 */ CREATE PROC sp_autoIdx @ifexec CHAR(1) AS BEGIN DECLARE @TB NVARCHAR(40); DECLARE @SQLON VARCHAR(200) , @SQLINCLUDE VARCHAR(200); DECLARE CUR CURSOR FOR SELECT DISTINCT statement FROM sys.dm_db_missing_index_details AS MID CROSS APPLY sys.dm_db_missing_index_columns(MID.index_handle) INNER JOIN sys.dm_db_missing_index_groups AS MIG ON MIG.index_handle = MID.index_handle ORDER BY statement ASC; OPEN CUR; FETCH CUR INTO @TB; WHILE ( @@FETCH_STATUS = 0 ) BEGIN PRINT ‘--‘ + @TB + ‘:‘; SET @SQLON = ‘CREATE INDEX IDX_‘ + UPPER(PARSENAME(@TB, 1)) + ‘_‘ + REPLACE(CAST(NEWID() AS VARCHAR(60)), ‘-‘, ‘_‘) + ‘ ON ‘ + @TB + ‘(‘; SET @SQLINCLUDE = ‘ INCLDE(‘; WITH T AS ( SELECT mig.* , statement AS table_name , column_id , column_name , column_usage , rowid = ROW_NUMBER() OVER ( PARTITION BY index_group_handle, statement ORDER BY index_group_handle, CASE column_usage WHEN ‘EQUALITY‘ THEN 1 WHEN ‘INEQUALITY‘ THEN 2 ELSE 3 END ) , levelid = RANK() OVER ( PARTITION BY index_group_handle, statement ORDER BY index_group_handle ) FROM sys.dm_db_missing_index_details AS mid CROSS APPLY sys.dm_db_missing_index_columns(mid.index_handle) INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle ) SELECT @SQLON = @SQLON + CASE WHEN column_usage IN ( ‘EQUALITY‘, ‘INEQUALITY‘ ) THEN column_name + ‘,‘ ELSE ‘‘ END , @SQLINCLUDE = @SQLINCLUDE + CASE WHEN column_usage = ‘INCLUDE‘ THEN column_name + ‘,‘ ELSE ‘‘ END FROM T WHERE table_name = @TB AND levelid = 1; IF RIGHT(@SQLON, 1) = ‘(‘ PRINT ‘根据动态管理对象无法智能生成索引计划‘; ELSE BEGIN SET @SQLON = LEFT(@SQLON, LEN(@SQLON) - 1) + ‘)‘; IF RIGHT(@SQLINCLUDE, 1) = ‘(‘ PRINT @SQLON; IF @ifexec = ‘1‘ EXEC(@SQLON); ELSE BEGIN SET @SQLINCLUDE = LEFT(@SQLINCLUDE, LEN(@SQLINCLUDE) - 1) + ‘)‘; SET @SQLON = @SQLON + @SQLINCLUDE; PRINT @SQLON; IF @ifexec = ‘1‘ EXEC(@SQLON); END; END; FETCH CUR INTO @TB; END; CLOSE CUR; DEALLOCATE CUR; END; GO

方法二：（未验证可行性 转自： https://blog.csdn.net/iteye_18688/article/details/81717229）

 

  1. /*

  调用方法： exec p_helpindex ‘tb_test‘

　　　　　　　　-----drop proc p_helpindex

  1. <em id="__mceDel">*/</em>
  1. <span style="color: #0000ff;"><br>CREATE</span> <span style="color: #0000ff;">PROC</span><span style="color: #000000;"> p_helpindex
  2.     </span><span style="color: #008000;">@tbname</span> sysname <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘‘</span><span style="color: #000000;"> ,
  3.     </span><span style="color: #008000;">@CLUSTERED</span> <span style="color: #0000ff;">INT</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘</span>
  4. <span style="color: #0000ff;">AS</span> <span style="color: #008080;">--</span><span style="color: #008080;">生成索引信息及索引创建脚本（Sql Server 2000）</span>
  5.  
  6.     <span style="color: #0000ff;">IF</span> <span style="color: #008000;">@tbname</span> <span style="color: #0000ff;">IS</span> <span style="color: #0000ff;">NULL</span>
  7.         <span style="color: #808080;">OR</span> <span style="color: #008000;">@tbname</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘‘</span>
  8.         <span style="color: #0000ff;">RETURN</span> <span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
  9.     </span><span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@t</span> <span style="color: #0000ff;">TABLE</span><span style="color: #000000;">
  10.         (
  11.           table_name </span><span style="color: #0000ff;">NVARCHAR</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">) ,
  12.           schema_name </span><span style="color: #0000ff;">NVARCHAR</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">) ,
  13.           fill_factor </span><span style="color: #0000ff;">INT</span><span style="color: #000000;"> ,
  14.           is_padded </span><span style="color: #0000ff;">INT</span><span style="color: #000000;"> ,
  15.           ix_name </span><span style="color: #0000ff;">NVARCHAR</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">) ,
  16.           type </span><span style="color: #0000ff;">INT</span><span style="color: #000000;"> ,
  17.           keyno </span><span style="color: #0000ff;">INT</span><span style="color: #000000;"> ,
  18.           column_name </span><span style="color: #0000ff;">NVARCHAR</span>(<span style="color: #800000; font-weight: bold;">200</span><span style="color: #000000;">) ,
  19.           cluster </span><span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">) ,
  20.           ignore_dupkey </span><span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">) ,
  21.           </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">unique</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">) ,
  22.           groupfile </span><span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">)
  23.         );
  24.     </span><span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@table_name</span> <span style="color: #0000ff;">NVARCHAR</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">) ,
  25.         </span><span style="color: #008000;">@schema_name</span> <span style="color: #0000ff;">NVARCHAR</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">) ,
  26.         </span><span style="color: #008000;">@fill_factor</span> <span style="color: #0000ff;">INT</span><span style="color: #000000;"> ,
  27.         </span><span style="color: #008000;">@is_padded</span> <span style="color: #0000ff;">INT</span><span style="color: #000000;"> ,
  28.         </span><span style="color: #008000;">@ix_name</span> <span style="color: #0000ff;">NVARCHAR</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">) ,
  29.         </span><span style="color: #008000;">@ix_name_old</span> <span style="color: #0000ff;">NVARCHAR</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">) ,
  30.         </span><span style="color: #008000;">@type</span> <span style="color: #0000ff;">INT</span><span style="color: #000000;"> ,
  31.         </span><span style="color: #008000;">@keyno</span> <span style="color: #0000ff;">INT</span><span style="color: #000000;"> ,
  32.         </span><span style="color: #008000;">@column_name</span> <span style="color: #0000ff;">NVARCHAR</span>(<span style="color: #800000; font-weight: bold;">100</span>) ,<span style="color: #008080;">--</span><span style="color: #008080;">@column_name_temp nvarchar(500),</span>
  33.         <span style="color: #008000;">@cluster</span> <span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">) ,
  34.         </span><span style="color: #008000;">@ignore_dupkey</span> <span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">) ,
  35.         </span><span style="color: #008000;">@unique</span> <span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">) ,
  36.         </span><span style="color: #008000;">@groupfile</span> <span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">);
  37.     </span><span style="color: #0000ff;">DECLARE</span> ms_crs_ind <span style="color: #0000ff;">CURSOR</span><span style="color: #000000;"> LOCAL STATIC
  38.     </span><span style="color: #0000ff;">FOR</span>
  39.         <span style="color: #0000ff;">SELECT</span>
  40. <span style="color: #0000ff;">DISTINCT</span>        table_name <span style="color: #808080;">=</span><span style="color: #000000;"> a.name ,
  41.                 schema_name </span><span style="color: #808080;">=</span><span style="color: #000000;"> b.name ,
  42.                 fill_factor </span><span style="color: #808080;">=</span><span style="color: #000000;"> c.OrigFillFactor ,
  43.                 is_padded </span><span style="color: #808080;">=</span> <span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> c.status <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">256</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span>
  44.                                  <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span>
  45.                             <span style="color: #0000ff;">END</span><span style="color: #000000;"> ,
  46.                 ix_name </span><span style="color: #808080;">=</span><span style="color: #000000;"> c.name ,
  47.                 type </span><span style="color: #808080;">=</span><span style="color: #000000;"> c.indid ,
  48.                 d.keyno ,
  49.                 column_name </span><span style="color: #808080;">=</span><span style="color: #000000;"> e.name
  50.                 </span><span style="color: #808080;">+</span> <span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span><span style="color: #000000;"> INDEXKEY_PROPERTY(a.id, c.indid, d.keyno,
  51.                                               </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">isdescending</span><span style="color: #ff0000;">‘</span>) <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span>
  52.                        <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> desc </span><span style="color: #ff0000;">‘</span>
  53.                        <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘‘</span>
  54.                   <span style="color: #0000ff;">END</span><span style="color: #000000;"> ,
  55.                 </span><span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> ( c.status <span style="color: #808080;">&</span> <span style="color: #800000; font-weight: bold;">16</span> ) <span style="color: #808080;"><></span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">clustered</span><span style="color: #ff0000;">‘</span>
  56.                      <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">nonclustered</span><span style="color: #ff0000;">‘</span>
  57.                 <span style="color: #0000ff;">END</span><span style="color: #000000;"> ,
  58.                 </span><span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> ( c.status <span style="color: #808080;">&</span> <span style="color: #800000; font-weight: bold;">1</span> ) <span style="color: #808080;"><></span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">IGNORE_DUP_KEY</span><span style="color: #ff0000;">‘</span>
  59.                      <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘‘</span>
  60.                 <span style="color: #0000ff;">END</span><span style="color: #000000;"> ,
  61.                 </span><span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> ( c.status <span style="color: #808080;">&</span> <span style="color: #800000; font-weight: bold;">2</span> ) <span style="color: #808080;"><></span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">unique</span><span style="color: #ff0000;">‘</span>
  62.                      <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘‘</span>
  63.                 <span style="color: #0000ff;">END</span><span style="color: #000000;"> ,
  64.                 g.groupname
  65.         </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    sysobjects a
  66.                 </span><span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> sysusers b <span style="color: #0000ff;">ON</span> a.uid <span style="color: #808080;">=</span><span style="color: #000000;"> b.uid
  67.                 </span><span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> sysindexes c <span style="color: #0000ff;">ON</span> a.id <span style="color: #808080;">=</span><span style="color: #000000;"> c.id
  68.                 </span><span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> sysindexkeys d <span style="color: #0000ff;">ON</span> a.id <span style="color: #808080;">=</span><span style="color: #000000;"> d.id
  69.                                              </span><span style="color: #808080;">AND</span> c.indid <span style="color: #808080;">=</span><span style="color: #000000;"> d.indid
  70.                 </span><span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> syscolumns e <span style="color: #0000ff;">ON</span> a.id <span style="color: #808080;">=</span><span style="color: #000000;"> e.id
  71.                                            </span><span style="color: #808080;">AND</span> d.colid <span style="color: #808080;">=</span><span style="color: #000000;"> e.colid
  72.                 </span><span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> sysfilegroups g <span style="color: #0000ff;">ON</span> g.groupid <span style="color: #808080;">=</span><span style="color: #000000;"> c.groupid
  73.                 </span><span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> master.dbo.spt_values f <span style="color: #0000ff;">ON</span> f.<span style="color: #0000ff;">number</span> <span style="color: #808080;">=</span><span style="color: #000000;"> c.status
  74.                                                      </span><span style="color: #808080;">AND</span> f.type <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">I</span><span style="color: #ff0000;">‘</span>
  75.         <span style="color: #0000ff;">WHERE</span>   a.id <span style="color: #808080;">=</span> <span style="color: #ff00ff;">OBJECT_ID</span>(<span style="color: #008000;">@tbname</span><span style="color: #000000;">)
  76.                 </span><span style="color: #808080;">AND</span> c.indid <span style="color: #808080;"><</span> <span style="color: #800000; font-weight: bold;">255</span>
  77.                 <span style="color: #808080;">AND</span> ( c.status <span style="color: #808080;">&</span> <span style="color: #800000; font-weight: bold;">64</span> ) <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">0</span>
  78.                 <span style="color: #808080;">AND</span> c.indid <span style="color: #808080;">>=</span> <span style="color: #008000;">@CLUSTERED</span>
  79.         <span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> c.indid ,
  80.                 d.keyno;
  81.     </span><span style="color: #0000ff;">OPEN</span><span style="color: #000000;"> ms_crs_ind;
  82.     </span><span style="color: #0000ff;">FETCH</span> ms_crs_ind <span style="color: #0000ff;">INTO</span> <span style="color: #008000;">@table_name</span>, <span style="color: #008000;">@schema_name</span>, <span style="color: #008000;">@fill_factor</span>, <span style="color: #008000;">@is_padded</span><span style="color: #000000;">,
  83.         </span><span style="color: #008000;">@ix_name</span>, <span style="color: #008000;">@type</span>, <span style="color: #008000;">@keyno</span>, <span style="color: #008000;">@column_name</span>, <span style="color: #008000;">@cluster</span>, <span style="color: #008000;">@ignore_dupkey</span><span style="color: #000000;">,
  84.         </span><span style="color: #008000;">@unique</span>, <span style="color: #008000;">@groupfile</span><span style="color: #000000;">;
  85.     </span><span style="color: #0000ff;">IF</span> <span style="color: #008000; font-weight: bold;">@@fetch_status</span> <span style="color: #808080;"><</span> <span style="color: #800000; font-weight: bold;">0</span>
  86.         <span style="color: #0000ff;">BEGIN</span>
  87.             <span style="color: #0000ff;">DEALLOCATE</span><span style="color: #000000;"> ms_crs_ind;
  88.             </span><span style="color: #0000ff;">RAISERROR</span>(<span style="color: #800000; font-weight: bold;">15472</span>,<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span>); <span style="color: #008080;">--</span><span style="color: #008080;">‘Object does not have any indexes.‘--无效索引（即没有键列的索引）</span>
  89.             <span style="color: #0000ff;">RETURN</span> <span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
  90.         </span><span style="color: #0000ff;">END</span><span style="color: #000000;">;
  91.     </span><span style="color: #0000ff;">WHILE</span> <span style="color: #008000; font-weight: bold;">@@fetch_status</span> <span style="color: #808080;">>=</span> <span style="color: #800000; font-weight: bold;">0</span>
  92.         <span style="color: #0000ff;">BEGIN</span>
  93.             <span style="color: #0000ff;">IF</span> <span style="color: #808080;">EXISTS</span> ( <span style="color: #0000ff;">SELECT</span>  <span style="color: #800000; font-weight: bold;">1</span>
  94.                         <span style="color: #0000ff;">FROM</span>    <span style="color: #008000;">@t</span>
  95.                         <span style="color: #0000ff;">WHERE</span>   ix_name <span style="color: #808080;">=</span> <span style="color: #008000;">@ix_name</span><span style="color: #000000;"> )
  96.                 </span><span style="color: #0000ff;">UPDATE</span>  <span style="color: #008000;">@t</span>
  97.                 <span style="color: #0000ff;">SET</span>     column_name <span style="color: #808080;">=</span> column_name <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #008000;">@column_name</span>
  98.                 <span style="color: #0000ff;">WHERE</span>   ix_name <span style="color: #808080;">=</span> <span style="color: #008000;">@ix_name</span><span style="color: #000000;">;
  99.             </span><span style="color: #0000ff;">ELSE</span>
  100.                 <span style="color: #0000ff;">INSERT</span>  <span style="color: #0000ff;">INTO</span> <span style="color: #008000;">@t</span>
  101.                         <span style="color: #0000ff;">SELECT</span>  <span style="color: #008000;">@table_name</span><span style="color: #000000;"> ,
  102.                                 </span><span style="color: #008000;">@schema_name</span><span style="color: #000000;"> ,
  103.                                 </span><span style="color: #008000;">@fill_factor</span><span style="color: #000000;"> ,
  104.                                 </span><span style="color: #008000;">@is_padded</span><span style="color: #000000;"> ,
  105.                                 </span><span style="color: #008000;">@ix_name</span><span style="color: #000000;"> ,
  106.                                 </span><span style="color: #008000;">@type</span><span style="color: #000000;"> ,
  107.                                 </span><span style="color: #008000;">@keyno</span><span style="color: #000000;"> ,
  108.                                 </span><span style="color: #008000;">@column_name</span><span style="color: #000000;"> ,
  109.                                 </span><span style="color: #008000;">@cluster</span><span style="color: #000000;"> ,
  110.                                 </span><span style="color: #008000;">@ignore_dupkey</span><span style="color: #000000;"> ,
  111.                                 </span><span style="color: #008000;">@unique</span><span style="color: #000000;"> ,
  112.                                 </span><span style="color: #008000;">@groupfile</span><span style="color: #000000;">;
  113.             </span><span style="color: #0000ff;">FETCH</span> ms_crs_ind <span style="color: #0000ff;">INTO</span> <span style="color: #008000;">@table_name</span>, <span style="color: #008000;">@schema_name</span>, <span style="color: #008000;">@fill_factor</span><span style="color: #000000;">,
  114.                 </span><span style="color: #008000;">@is_padded</span>, <span style="color: #008000;">@ix_name</span>, <span style="color: #008000;">@type</span>, <span style="color: #008000;">@keyno</span>, <span style="color: #008000;">@column_name</span>, <span style="color: #008000;">@cluster</span><span style="color: #000000;">,
  115.                 </span><span style="color: #008000;">@ignore_dupkey</span>, <span style="color: #008000;">@unique</span>, <span style="color: #008000;">@groupfile</span><span style="color: #000000;">;
  116.         </span><span style="color: #0000ff;">END</span><span style="color: #000000;">;
  117.     </span><span style="color: #0000ff;">DEALLOCATE</span><span style="color: #000000;"> ms_crs_ind;
  118.     </span><span style="color: #0000ff;">SELECT</span>  <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">CREATE </span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">UPPER</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">unique</span><span style="color: #ff0000;">]</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">unique</span><span style="color: #ff0000;">]</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘‘</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘‘</span>
  119.                                                <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘</span> <span style="color: #ff0000;">‘</span>
  120.                                           <span style="color: #0000ff;">END</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">UPPER</span>(cluster) <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> INDEX </span><span style="color: #ff0000;">‘</span>
  121.             <span style="color: #808080;">+</span> ix_name <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> ON </span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> table_name <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">(</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> column_name <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">)</span><span style="color: #ff0000;">‘</span>
  122.             <span style="color: #808080;">+</span> <span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> fill_factor <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">0</span>
  123.                         <span style="color: #808080;">OR</span> is_padded <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span>
  124.                         <span style="color: #808080;">OR</span> ( <span style="color: #ff00ff;">UPPER</span>(cluster) <span style="color: #808080;">!=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">NONCLUSTERED</span><span style="color: #ff0000;">‘</span>
  125.                              <span style="color: #808080;">AND</span> ignore_dupkey <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">IGNORE_DUP_KEY</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
  126.                            )
  127.                    </span><span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> WITH </span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> is_padded <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">PAD_INDEX,</span><span style="color: #ff0000;">‘</span>
  128.                                         <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘‘</span>
  129.                                    <span style="color: #0000ff;">END</span>
  130.                         <span style="color: #808080;">+</span> <span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> fill_factor <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">0</span>
  131.                                <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">FILLFACTOR =</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">LTRIM</span><span style="color: #000000;">(fill_factor)
  132.                                </span><span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘‘</span>
  133.                           <span style="color: #0000ff;">END</span>
  134.                         <span style="color: #808080;">+</span> <span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> ignore_dupkey <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">IGNORE_DUP_KEY</span><span style="color: #ff0000;">‘</span>
  135.                                     <span style="color: #808080;">AND</span> <span style="color: #ff00ff;">UPPER</span>(cluster) <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">NONCLUSTERED</span><span style="color: #ff0000;">‘</span>
  136.                                <span style="color: #0000ff;">THEN</span> <span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> ( fill_factor <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">0</span>
  137.                                                 <span style="color: #808080;">OR</span> is_padded <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  138.                                               ) </span><span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,IGNORE_DUP_KEY</span><span style="color: #ff0000;">‘</span>
  139.                                          <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">,IGNORE_DUP_KEY</span><span style="color: #ff0000;">‘</span>
  140.                                     <span style="color: #0000ff;">END</span>
  141.                                <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘‘</span>
  142.                           <span style="color: #0000ff;">END</span>
  143.                    <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘‘</span>
  144.               <span style="color: #0000ff;">END</span> <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> ON [</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> groupfile <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">]</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">AS</span><span style="color: #000000;"> col
  145.     </span><span style="color: #0000ff;">FROM</span>    <span style="color: #008000;">@t</span><span style="color: #000000;">;
  146.     </span><span style="color: #0000ff;">RETURN</span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">;
  147. </span><span style="color: #0000ff;">GO</span>

 

方法三：（未验证可行性 转自：https://www.cnblogs.com/yy3b2007com/p/4541405.html）

  1. <span style="color: #008080;">--</span><span style="color: #008080;">1. get all indexes from current db, place in temp table</span><span style="color: #008080;">
  2. --</span><span style="color: #008080;">一。从当前数据库中获取所有索引，放到临时表中</span>
  3. <span style="color: #0000ff;">SELECT</span>  schemaName <span style="color: #808080;">=</span><span style="color: #000000;"> s.name ,
  4.         tablename </span><span style="color: #808080;">=</span> <span style="color: #ff00ff;">OBJECT_NAME</span><span style="color: #000000;">(i.id) ,
  5.         tableid </span><span style="color: #808080;">=</span><span style="color: #000000;"> i.id ,
  6.         indexid </span><span style="color: #808080;">=</span><span style="color: #000000;"> i.indid ,
  7.         indexname </span><span style="color: #808080;">=</span><span style="color: #000000;"> i.name ,
  8.         i.status ,
  9.         isunique </span><span style="color: #808080;">=</span> <span style="color: #ff00ff;">INDEXPROPERTY</span>(i.id, i.name, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">isunique</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">) ,
  10.         isclustered </span><span style="color: #808080;">=</span> <span style="color: #ff00ff;">INDEXPROPERTY</span>(i.id, i.name, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">isclustered</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">) ,
  11.         indexfillfactor </span><span style="color: #808080;">=</span> <span style="color: #ff00ff;">INDEXPROPERTY</span>(i.id, i.name, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">indexfillfactor</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  12. </span><span style="color: #0000ff;">INTO</span><span style="color: #000000;">    #tmp_indexes
  13. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    sysindexes i
  14.         </span><span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> sys.tables t <span style="color: #0000ff;">ON</span> i.id <span style="color: #808080;">=</span> t.<span style="color: #ff00ff;">object_id</span>
  15.         <span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> sys.schemas s <span style="color: #0000ff;">ON</span> t.schema_id <span style="color: #808080;">=</span><span style="color: #000000;"> s.schema_id
  16. </span><span style="color: #0000ff;">WHERE</span>   i.indid <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">0</span>
  17.         <span style="color: #808080;">AND</span> i.indid <span style="color: #808080;"><</span> <span style="color: #800000; font-weight: bold;">255</span>                      <span style="color: #008080;">--</span><span style="color: #008080;">not certain about this</span>
  18.         <span style="color: #808080;">AND</span> ( i.status <span style="color: #808080;">&</span> <span style="color: #800000; font-weight: bold;">64</span> ) <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">;
  19.                                  </span><span style="color: #008080;">--</span><span style="color: #008080;">existing indexes  --现有索引</span>
  20. <span style="color: #008080;">--</span><span style="color: #008080;">add additional columns to store include and key column lists</span><span style="color: #008080;">
  21. --</span><span style="color: #008080;">添加其他列以存储包含列和键列列表</span>
  22. <span style="color: #0000ff;">ALTER</span> <span style="color: #0000ff;">TABLE</span> #tmp_indexes <span style="color: #0000ff;">ADD</span> keycolumns <span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">4000</span>), includes <span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">4000</span><span style="color: #000000;">);
  23. </span><span style="color: #0000ff;">GO</span>
  24. <span style="color: #008080;">--</span><span style="color: #008080;">################################################################################################</span><span style="color: #008080;">
  25. --</span><span style="color: #008080;">2. loop through tables, put include and index columns into variables</span><span style="color: #008080;">
  26. --</span><span style="color: #008080;">2。遍历表，将include和index列放入变量中</span>
  27. <span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@isql_key</span> <span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">4000</span><span style="color: #000000;">) ,
  28.     </span><span style="color: #008000;">@isql_incl</span> <span style="color: #0000ff;">VARCHAR</span>(<span style="color: #800000; font-weight: bold;">4000</span><span style="color: #000000;">) ,
  29.     </span><span style="color: #008000;">@tableid</span> <span style="color: #0000ff;">INT</span><span style="color: #000000;"> ,
  30.     </span><span style="color: #008000;">@indexid</span> <span style="color: #0000ff;">INT</span><span style="color: #000000;">;
  31. </span><span style="color: #0000ff;">DECLARE</span> index_cursor <span style="color: #0000ff;">CURSOR</span>
  32. <span style="color: #0000ff;">FOR</span>
  33.     <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  tableid ,
  34.             indexid
  35.     </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    #tmp_indexes;  
  36. </span><span style="color: #0000ff;">OPEN</span><span style="color: #000000;"> index_cursor;
  37. </span><span style="color: #0000ff;">FETCH</span> <span style="color: #0000ff;">NEXT</span> <span style="color: #0000ff;">FROM</span> index_cursor <span style="color: #0000ff;">INTO</span> <span style="color: #008000;">@tableid</span>, <span style="color: #008000;">@indexid</span><span style="color: #000000;">;
  38. </span><span style="color: #0000ff;">WHILE</span> <span style="color: #008000; font-weight: bold;">@@fetch_status</span> <span style="color: #808080;"><></span> <span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">1</span>
  39.     <span style="color: #0000ff;">BEGIN</span>
  40.     
  41.         <span style="color: #0000ff;">SELECT</span>  <span style="color: #008000;">@isql_key</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘‘</span><span style="color: #000000;"> ,
  42.                 </span><span style="color: #008000;">@isql_incl</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘‘</span><span style="color: #000000;">;
  43.   
  44.         </span><span style="color: #0000ff;">SELECT</span> <span style="color: #008080;">--</span><span style="color: #008080;">i.name, sc.colid, sc.name, ic.index_id, ic.object_id, *</span><span style="color: #008080;">
  45. --</span><span style="color: #008080;">key column</span>
  46.                 <span style="color: #008000;">@isql_key</span> <span style="color: #808080;">=</span> <span style="color: #ff00ff;">CASE</span><span style="color: #000000;"> ic.is_included_column
  47.                               </span><span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">0</span>
  48.                               <span style="color: #0000ff;">THEN</span> <span style="color: #ff00ff;">CASE</span><span style="color: #000000;"> ic.is_descending_key
  49.                                      </span><span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">1</span>
  50.                                      <span style="color: #0000ff;">THEN</span> <span style="color: #008000;">@isql_key</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">COALESCE</span>(sc.name, <span style="color: #ff0000;">‘‘</span><span style="color: #000000;">)
  51.                                           </span><span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> DESC, </span><span style="color: #ff0000;">‘</span>
  52.                                      <span style="color: #0000ff;">ELSE</span> <span style="color: #008000;">@isql_key</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">COALESCE</span>(sc.name, <span style="color: #ff0000;">‘‘</span><span style="color: #000000;">)
  53.                                           </span><span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> ASC, </span><span style="color: #ff0000;">‘</span>
  54.                                    <span style="color: #0000ff;">END</span>
  55.                               <span style="color: #0000ff;">ELSE</span> <span style="color: #008000;">@isql_key</span>
  56.                             <span style="color: #0000ff;">END</span><span style="color: #000000;"> ,
  57. </span><span style="color: #008080;">--</span><span style="color: #008080;">include column</span>
  58.                 <span style="color: #008000;">@isql_incl</span> <span style="color: #808080;">=</span> <span style="color: #ff00ff;">CASE</span><span style="color: #000000;"> ic.is_included_column
  59.                                </span><span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">1</span>
  60.                                <span style="color: #0000ff;">THEN</span> <span style="color: #ff00ff;">CASE</span><span style="color: #000000;"> ic.is_descending_key
  61.                                       </span><span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">1</span>
  62.                                       <span style="color: #0000ff;">THEN</span> <span style="color: #008000;">@isql_incl</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">COALESCE</span>(sc.name, <span style="color: #ff0000;">‘‘</span><span style="color: #000000;">)
  63.                                            </span><span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">, </span><span style="color: #ff0000;">‘</span>
  64.                                       <span style="color: #0000ff;">ELSE</span> <span style="color: #008000;">@isql_incl</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">COALESCE</span>(sc.name, <span style="color: #ff0000;">‘‘</span><span style="color: #000000;">)
  65.                                            </span><span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">, </span><span style="color: #ff0000;">‘</span>
  66.                                     <span style="color: #0000ff;">END</span>
  67.                                <span style="color: #0000ff;">ELSE</span> <span style="color: #008000;">@isql_incl</span>
  68.                              <span style="color: #0000ff;">END</span>
  69.         <span style="color: #0000ff;">FROM</span><span style="color: #000000;">    sysindexes i
  70.                 </span><span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> sys.index_columns <span style="color: #0000ff;">AS</span> ic <span style="color: #0000ff;">ON</span> ( ic.column_id <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">0</span>
  71.                                                         <span style="color: #808080;">AND</span> ( ic.key_ordinal <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">0</span>
  72.                                                               <span style="color: #808080;">OR</span> ic.partition_ordinal <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">0</span>
  73.                                                               <span style="color: #808080;">OR</span> ic.is_included_column <span style="color: #808080;">!=</span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">
  74.                                                             )
  75.                                                       )
  76.                                                       </span><span style="color: #808080;">AND</span> ( ic.index_id <span style="color: #808080;">=</span> <span style="color: #ff00ff;">CAST</span>(i.indid <span style="color: #0000ff;">AS</span> <span style="color: #0000ff;">INT</span><span style="color: #000000;">)
  77.                                                             </span><span style="color: #808080;">AND</span> ic.<span style="color: #ff00ff;">object_id</span> <span style="color: #808080;">=</span><span style="color: #000000;"> i.id
  78.                                                           )
  79.                 </span><span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> sys.columns <span style="color: #0000ff;">AS</span> sc <span style="color: #0000ff;">ON</span> sc.<span style="color: #ff00ff;">object_id</span> <span style="color: #808080;">=</span> ic.<span style="color: #ff00ff;">object_id</span>
  80.                                                 <span style="color: #808080;">AND</span> sc.column_id <span style="color: #808080;">=</span><span style="color: #000000;"> ic.column_id
  81.         </span><span style="color: #0000ff;">WHERE</span>   i.indid <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">0</span>
  82.                 <span style="color: #808080;">AND</span> i.indid <span style="color: #808080;"><</span> <span style="color: #800000; font-weight: bold;">255</span>
  83.                 <span style="color: #808080;">AND</span> ( i.status <span style="color: #808080;">&</span> <span style="color: #800000; font-weight: bold;">64</span> ) <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">0</span>
  84.                 <span style="color: #808080;">AND</span> i.id <span style="color: #808080;">=</span> <span style="color: #008000;">@tableid</span>
  85.                 <span style="color: #808080;">AND</span> i.indid <span style="color: #808080;">=</span> <span style="color: #008000;">@indexid</span>
  86.         <span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> i.name ,
  87.                 </span><span style="color: #ff00ff;">CASE</span><span style="color: #000000;"> ic.is_included_column
  88.                   </span><span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">THEN</span><span style="color: #000000;"> ic.index_column_id
  89.                   </span><span style="color: #0000ff;">ELSE</span><span style="color: #000000;"> ic.key_ordinal
  90.                 </span><span style="color: #0000ff;">END</span><span style="color: #000000;">;
  91.     
  92.         </span><span style="color: #0000ff;">IF</span> <span style="color: #ff00ff;">LEN</span>(<span style="color: #008000;">@isql_key</span>) <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">1</span>
  93.             <span style="color: #0000ff;">SET</span> <span style="color: #008000;">@isql_key</span> <span style="color: #808080;">=</span> <span style="color: #808080;">LEFT</span>(<span style="color: #008000;">@isql_key</span>, <span style="color: #ff00ff;">LEN</span>(<span style="color: #008000;">@isql_key</span>) <span style="color: #808080;">-</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">);
  94.    
  95.         </span><span style="color: #0000ff;">IF</span> <span style="color: #ff00ff;">LEN</span>(<span style="color: #008000;">@isql_incl</span>) <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">1</span>
  96.             <span style="color: #0000ff;">SET</span> <span style="color: #008000;">@isql_incl</span> <span style="color: #808080;">=</span> <span style="color: #808080;">LEFT</span>(<span style="color: #008000;">@isql_incl</span>, <span style="color: #ff00ff;">LEN</span>(<span style="color: #008000;">@isql_incl</span>) <span style="color: #808080;">-</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">);
  97.   
  98.         </span><span style="color: #0000ff;">UPDATE</span><span style="color: #000000;">  #tmp_indexes
  99.         </span><span style="color: #0000ff;">SET</span>     keycolumns <span style="color: #808080;">=</span> <span style="color: #008000;">@isql_key</span><span style="color: #000000;"> ,
  100.                 includes </span><span style="color: #808080;">=</span> <span style="color: #008000;">@isql_incl</span>
  101.         <span style="color: #0000ff;">WHERE</span>   tableid <span style="color: #808080;">=</span> <span style="color: #008000;">@tableid</span>
  102.                 <span style="color: #808080;">AND</span> indexid <span style="color: #808080;">=</span> <span style="color: #008000;">@indexid</span><span style="color: #000000;">;
  103.  
  104.         </span><span style="color: #0000ff;">FETCH</span> <span style="color: #0000ff;">NEXT</span> <span style="color: #0000ff;">FROM</span> index_cursor <span style="color: #0000ff;">INTO</span> <span style="color: #008000;">@tableid</span>, <span style="color: #008000;">@indexid</span><span style="color: #000000;">;
  105.  
  106.     </span><span style="color: #0000ff;">END</span><span style="color: #000000;">;
  107. </span><span style="color: #0000ff;">CLOSE</span><span style="color: #000000;"> index_cursor;
  108. </span><span style="color: #0000ff;">DEALLOCATE</span><span style="color: #000000;"> index_cursor;
  109. </span><span style="color: #008080;">--</span><span style="color: #008080;">remove invalid indexes,ie ones without key columns</span><span style="color: #008080;">
  110. --</span><span style="color: #008080;">删除无效索引（即没有键列的索引）</span>
  111. <span style="color: #0000ff;">DELETE</span>  <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> #tmp_indexes
  112. </span><span style="color: #0000ff;">WHERE</span>   keycolumns <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘‘</span><span style="color: #000000;">;
  113. </span><span style="color: #008080;">--</span><span style="color: #008080;">################################################################################################</span><span style="color: #008080;">
  114. --</span><span style="color: #008080;">3. output the index creation scripts</span><span style="color: #008080;">
  115. --</span><span style="color: #008080;">三。输出索引创建脚本</span>
  116. <span style="color: #0000ff;">SET</span> NOCOUNT <span style="color: #0000ff;">ON</span><span style="color: #000000;">;
  117. </span><span style="color: #008080;">--</span><span style="color: #008080;">separator</span>
  118. <span style="color: #0000ff;">SELECT</span>  <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">---------------------------------------------------------------------</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  119. </span><span style="color: #008080;">--</span><span style="color: #008080;">create index scripts (for backup)</span><span style="color: #008080;">
  120. --</span><span style="color: #008080;">创建索引脚本（用于备份）</span>
  121. <span style="color: #0000ff;">SELECT</span>  <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">CREATE </span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> isunique <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">UNIQUE </span><span style="color: #ff0000;">‘</span>
  122.                          <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘‘</span>
  123.                     <span style="color: #0000ff;">END</span> <span style="color: #808080;">+</span> <span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> isclustered <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">CLUSTERED </span><span style="color: #ff0000;">‘</span>
  124.                                <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘‘</span>
  125.                           <span style="color: #0000ff;">END</span> <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">INDEX [</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> indexname <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">]</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> ON [</span><span style="color: #ff0000;">‘</span>
  126.         <span style="color: #808080;">+</span> schemaName <span style="color: #808080                    </div>
  127.                   
  128.  
  129.                     <div class=" "="">
  130.                         <ul class="m-news-opt fix">
  131.                             <li class="opt-item">
  132.                                 <a href="/sql_question-384368.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">MySQL ------  相关概念简介（DBMS ，MySQL连接）（十八）</p></a>
  133.                             </li>
  134.                             <li class="opt-item ta-r">
  135.                                  <a href="/sql_question-384370.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">docker部署springboot前后端分离项目（jdk+jar包+mysql+redis+nginx）</p></a>
  136.                             </li>
  137.                         </ul>
  138.                     </span>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！