Como gerar o script de todos os índices do banco de dados SQL SERVER

por viniciusfonsecadba, publicado em Administração, backup, DBA, Dicas, Scripts, SQL Server

Fala pessoal, tudo bem?

É importantíssimo realizar a avaliação recorrente dos índices das tabelas dos seus bancos de dados, principalmente quando você tem um ERP de um terceiro e precisa customizá-lo e criar seus próprios índices.

Esses índices normalmente criados por nós, em uma atualização do sistema, provavelmente serão dropados e serão recriados apenas de você tiver criado uma estratégia via job do SQL Agent para eles serem recriados, porém toda via, vc pode esquecer de incluir algum índice lá, ou você é um novato e nem sequer sabe desse processo, correto? É raro mas acontece com frequência, então que tal ter uma rotina que envia para vc periodicamente um script com todos os scripts de criação dos índices da sua base?

Abaixo tem um código que faz isso:

Para funcionar ai para você, vc precisa estar no contexto do banco de dados que vc deseja gerar os scripts (subtituir o USE dba pelo seu banco) e alterar as configurações para o envio do email no final do script, e logicamente ter habilitada uma conta e um profile no database mail.

USE dba

DECLARE @SchemaName varchar(100)declare @TableName varchar(256)
declare @IndexName varchar(256)
declare @ColumnName varchar(100)
declare @is_unique varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled varchar(100)
declare @IndexOptions varchar(max)
declare @IndexColumnId int
declare @IsDescendingKey int 
declare @IsIncludedColumn INT
DECLARE @TSQLScripCreationIndex varchar(max)
declare @TSQLScripDisableIndex varchar(max)
DECLARE @scriptindex VARCHAR(MAX) = ''
 DECLARE @HTML_Body VARCHAR(MAX)
 DECLARE @HTML_Head VARCHAR(MAX)
 DECLARE @HTML_Tail VARCHAR(MAX)
DECLARE @subject_db VARCHAR(MAX) = 'all index - ' + DB_NAME()

declare CursorIndex cursor for
 select schema_name(t.schema_id) [schema_name], t.name, ix.name,
 case when ix.is_unique = 1 then 'UNIQUE ' else '' END 
 , 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(ix.fill_factor AS VARCHAR(3)) AS IndexOptions
 , ix.is_disabled , FILEGROUP_NAME(ix.data_space_id) FileGroupName
 from sys.tables t 
 inner join sys.indexes ix on t.object_id=ix.object_id
 where ix.type>0 and ix.is_primary_key=0 and ix.is_unique_constraint=0 --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
 order by schema_name(t.schema_id), t.name, ix.name

open CursorIndex
fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName

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
 

 set @TSQLScripCreationIndex =''
 set @TSQLScripDisableIndex =''
 set @TSQLScripCreationIndex='CREATE '+ @is_unique  +@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) + ';
  '  + CHAR(13) + CHAR(13) 

 if @is_disabled=1 
  set  @TSQLScripDisableIndex=  CHAR(13) +'ALTER INDEX ' +QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13) 

 SELECT @scriptindex = @scriptindex  + @TSQLScripCreationIndex
 SELECT @scriptindex = @scriptindex  + @TSQLScripDisableIndex
 
 fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName

end
close CursorIndex
deallocate CursorIndex

-- PRINT @scriptindex  -- Exibe na aba messages



 SET @HTML_Head = '<html>'
 SET @HTML_Head = @HTML_Head + '<head>' + CHAR(13) + CHAR(10) ;
 
 SET @HTML_Head = @HTML_Head + '</head>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + '<body><b>Index create script.</b><hr />' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + '<table>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <tr>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Indexes Script</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' </tr>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Tail = '</table></body></html>' ; 

 SET @HTML_Body = @HTML_Head + @scriptindex + @HTML_Tail


EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'DBA',
@recipients = 'vinicius.castro.fonseca@gmail.com', 
@subject = @subject_db, 
@body = @HTML_Body, 
@body_format = 'HTML' ;

O resultado é esse aí, não está muito bonito mais é bem funcional, útil e fácil do que gerar os scripts via object explorer details.

Além de receber por email, se procurar por “Exibe na aba messages” no script e descomentar esse print, o resultado será exibido na aba mensagens do SSMS.

A partir daí é só criar um job no SQL Agent e programar de acordo com o período que se adaptar melhor ao seu ambiente.

Talvez antes de uma gmud de atualização do ERP, seja bom executar esse job para garantir o scritp de criação. 🙂

Então é isso por hoje pessoal.

Abs e vamos e que vamos!

OBS: Se vc for ou souber quem é o autor desse script que gera o create index, me informe para eu atualizar aqui e dar os créditos, eu já o utilizo há algum tempo e apenas o alterei para fazer o que eu precisava.

(Edit) OBS 2: Se o seu caso é a criação de índices em um ERP de terceiro o ideal é que vc tenha uma nomenclatura padrão definida e que possa ser facilmente consultada para identificar todos os índices customizados, dessa forma você pode até customizar o script acima para gerar apenas esses índices.

Deixe um comentário