Thursday 11 October 2012

Script all indexes of a database

Does not Support advanced options (fill factor, options....)
Supports Include



  SELECT
 Identity(int, 1,1) Id,
 OBJECT_NAME([ixs].[object_id]) 'table_name'
 ,[ixs].[type] 'index_type'
 ,[ixs].[type_desc]
 ,[ixs].[name] 'index_name'
 ,[ixs].[is_unique]
 ,[ixs].[fill_factor]
 ,[ix_col].[key_ordinal]
 ,[col].[name] 'column_name'
 ,[ix_col].[is_descending_key]
 ,[is_included_column]

 INTO  #Indexes

 FROM
 [sys].[tables] tbls
 INNER JOIN [sys].[indexes] ixs
 ON [tbls].[object_id] = [ixs].[object_id]
 AND [tbls].[type] = 'U'
 INNER JOIN [sys].[index_columns] ix_col
 ON [ixs].[object_id] = [ix_col].[object_id]
 AND [ixs].[index_id] = [ix_col].[index_id]
 INNER JOIN [sys].[columns] col
 ON [col].[object_id] = [ix_col].[object_id]
 AND [col].[column_id] = [ix_col].[column_id]
 WHERE
 [ixs].[type] IN ( 1 , 2 )
 ORDER BY
 OBJECT_NAME([ixs].[object_id])
 ,[ixs].[type]
 ,[ixs].[name]
 ,[is_included_column]
 ,[ix_col].[key_ordinal]

 Declare @index_name varchar(500)
  
 Declare @tmp table (index_name varchar(500) primary key, isProcessed tinyInt default 0)
 insert into @tmp (index_name)
 select distinct Index_name from #Indexes

 Declare @cols varchar(max), @include varchar(max) 

 Declare @sql varchar(max)
  
 while (1 = 1)
 Begin
 select top 1 @index_name = index_name from @tmp where isProcessed = 0
 if (@@ROWCOUNT = 0) break;
 update @tmp set isProcessed = 2 where index_name = @index_name
  select @cols = '', @include = ''
 
select @cols = @cols + CASE WHEN is_included_column = 0 then 
', [' + column_name + '] ' + case when is_descending_key = 0 then 'ASC' else 'DESC' end 
else '' end,
@include = @include + CASE WHEN is_included_column = 1 then ', [' + column_name + ']' else '' end
from #indexes where index_name = @index_name order by Id  

Select top 1 @sql = 'IF NOT EXISTS (SELECT [Name] FROM [sys].[indexes] WHERE [name] = ''' + @index_name + ''') '  +

'CREATE ' + case when is_unique = '1' THEN 'UNIQUE '  ELSE '' END + type_desc + 'INDEX [' + @index_name + '] ON [' + table_name COLLATE Latin1_General_CI_AS  + '] (' + SUBSTRING(@cols, 2, 8000) + ' ) '
+ case when @include <> '' then 'INCLUDE ( ' + SUBSTRING(@include, 2, 8000) + ' )' else '' end
from #Indexes where index_name = @index_name  
Print @sql
 End 

 Drop table #Indexes

No comments:

Post a Comment