不用Object Explorer Script out all Indexes in DB Level

利用SSMS的UI Object Explorer 可以一次Script出Table下的Index，

但是如果要一次找出DB下的Index就會變得非常麻煩。

因此我參考此篇Blog的文章並改寫腳本。

原先的Blog中的腳本不包含

• 和Primary key, Unique key 有關的 Indexes
• Partition Table下的 indexes

改良後的腳本如下，目前Partition Table下的 indexes語法還有些許問題。

還望有前輩能協助改寫改良，目前語法中包含了Index的Option, Fillfactor, FileGroup等等

DECLARE @Index_Id             Varchar(4000)
DECLARE @Is_Primary_Key       INT
DECLARE @Is_Unique_Key        INT
DECLARE @Data_Space_id        INT
DECLARE @Is_Unique_constraint INT
DECLARE @SchemaName varchar(100)DECLARE @TableName varchar(256)
DECLARE @IndexName varchar(256)
DECLARE @ColumnName varchar(100)
DECLARE @IndexTypeDesc varchar(100)
DECLARE @FileGroupName varchar(100)
DECLARE @is_disabled varchar(100)
DECLARE @IndexOptions varchar(4000)
DECLARE @IndexColumnId int
DECLARE @IsDescENDingKey int 
DECLARE @IsIncludedColumn int
DECLARE @TSQLScripDisableIndex varchar(4000)




Print '###################################################################################################'+ CHAR(13) + CHAR(13) +
'#                  Please Aware Partition Table''s Index Script Is not Correct                     #'+ CHAR(13) + CHAR(13) +
'###################################################################################################'+ CHAR(13) + CHAR(13)
 


DECLARE CursorIndex CURSOR FOR
 SELECT schema_name(t.schema_id) [schema_name], t.name, ix.name
 , ix.type_desc,
 CASE WHEN ix.is_padded=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END
 + CASE WHEN ix.allow_page_locks=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END
 + CASE WHEN ix.allow_row_locks=1 THEN  'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END
 + CASE WHEN INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END
 + CASE WHEN ix.ignore_dup_key=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END
 + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST( CASE WHEN ix.fill_factor=0 THEN 100 ELSE  ix.fill_factor END AS VARCHAR(3)) AS IndexOptions
 , ix.is_disabled , FILEGROUP_NAME(CASE WHEN ix.data_space_id<=32767 THEN ix.data_space_id ELSE 1 END) FileGroupName  , index_id  , is_primary_key  , is_unique  , data_space_id  , is_unique_constraint  FROM sys.tables t   INNER JOIN sys.indexes ix on t.object_id=ix.object_id  WHERE ix.type>0 and  --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
 t.is_ms_shipped=0 and t.name<>'sysdiagrams'
-- and t.name=@name
 ORDER BY schema_name(t.schema_id), t.name, ix.name

OPEN CursorIndex
FETCH NEXT FROM CursorIndex into  @SchemaName, @TableName, @IndexName, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName, @Index_Id, @Is_Primary_Key, @Is_Unique_Key, @Data_Space_id, @Is_Unique_constraint

WHILE (@@fetch_status=0)
BEGIN
 DECLARE @IndexColumns varchar(max)
 DECLARE @IncludedColumns varchar(max)
 
 SET @IndexColumns=''
 SET @IncludedColumns=''
 
 DECLARE CursorIndexColumn CURSOR FOR 
  SELECT col.name, ixc.is_descENDing_key, ixc.is_included_column
  FROM sys.tables tb 
  INNER JOIN sys.indexes ix on tb.object_id=ix.object_id
  INNER JOIN sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
  INNER JOIN sys.columns col on ixc.object_id =col.object_id  and ixc.column_id=col.column_id
  WHERE ix.type>0 and (ix.is_primary_key=0 or ix.is_unique_constraint=0)
  and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName
  ORDER BY ixc.index_column_id
 
 OPEN CursorIndexColumn 
 FETCH NEXT FROM CursorIndexColumn into  @ColumnName, @IsDescENDingKey, @IsIncludedColumn
 
 WHILE (@@fetch_status=0)
 BEGIN
  IF @IsIncludedColumn=0 
   SET @IndexColumns=@IndexColumns + @ColumnName  + CASE WHEN @IsDescENDingKey=1  THEN ' DESC, ' ELSE  ' ASC, ' END
  ELSE 
   SET @IncludedColumns=@IncludedColumns  + @ColumnName  +', ' 

  FETCH NEXT FROM CursorIndexColumn into @ColumnName, @IsDescENDingKey, @IsIncludedColumn
 END

 CLOSE CursorIndexColumn
 DEALLOCATE CursorIndexColumn

 SET @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
 SET @IncludedColumns = CASE WHEN len(@IncludedColumns) >0 THEN substring(@IncludedColumns, 1, len(@IncludedColumns)-1) ELSE '' END
 --  print @IndexColumns
 --  print @IncludedColumns


 IF @Is_Unique_Key=0 or (@Is_Unique_constraint=0 and @Is_Primary_Key=0)
 BEGIN

 PRINT 'CREATE '+ CASE WHEN @Is_Unique_Key = 1 THEN 'UNIQUE ' ELSE '' END   +@IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ '('+@IndexColumns+') '+ 
			CASE WHEN len(@IncludedColumns)>0 THEN CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' ELSE '' END + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';' + CASE WHEN @Data_Space_id>32367 THEN 'PARTITION INDEX' ELSE '' END  + CHAR(13) + CHAR(13) +
            '------------------------------------------------------------------------------------'+ CHAR(13)  
 END

 ELSE IF @Is_Primary_Key=1 
 BEGIN
 PRINT  'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] ' +
            'ADD CONSTRAINT ['+@IndexName+']' +' PRIMARY KEY ' +@IndexTypeDesc+ CHAR(13) + '(' + CHAR(13) + @IndexColumns + CHAR(13) + ')WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';' + CASE WHEN @Data_Space_id>32367 THEN 'PARTITION INDEX' ELSE '' END  + CHAR(13) + CHAR(13) +
            '------------------------------------------------------------------------------------'+ CHAR(13) 
 END

 ELSE IF @Is_Primary_Key=0 and @Is_Unique_Key=1
 BEGIN
 PRINT 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] ' +
           'ADD CONSTRAINT ['+@IndexName+']' +' UNIQUE ' +@IndexTypeDesc+ CHAR(13) + '(' + CHAR(13) + @IndexColumns + CHAR(13) + ')WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'+ CASE WHEN @Data_Space_id>32367 THEN 'PARTITION INDEX' ELSE '' END  + CHAR(13) + CHAR(13) +
           '------------------------------------------------------------------------------------'+ CHAR(13)  
 END




 IF @is_disabled=1 
 BEGIN
 PRINT 'ALTER INDEX ' +QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DISABLE;' + CASE WHEN @Data_Space_id>32367 THEN 'PARTITION INDEX' ELSE '' END  + CHAR(13) + CHAR(13) +
            '------------------------------------------------------------------------------------'+ CHAR(13)  
 END
 


 FETCH NEXT FROM CursorIndex into  @SchemaName, @TableName, @IndexName, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName, @Index_Id, @Is_Primary_Key, @Is_Unique_Key, @Data_Space_id, @Is_Unique_constraint

END
CLOSE CursorIndex
DEALLOCATE CursorIndex