Gerando scripts de drop e create de índices e estatísticas

E ai galera, tudo na paz? Espero que sim. Vamos lá com mais um post prático. Semana passada recebemos uma demanda do pessoal que dar suporte ao ERP de um ambiente que administramos.  Eles necessitavam alterar o comprimento de um tipo de dados varchar de uma coluna e na tentativa executaram o comando ALTER TABLE Table ALTER COLUMN Colunm VARCHAR (50) NULL, no qual para variar o SQL Server retornou a mensagem:

Mensagem 5074, Nível 16, Estado 1, Linha 1
The index ‘idx_table_1’ is dependent on column ‘table’.
Mensagem 5074, Nível 16, Estado 1, Linha 1
The index ‘table_dt_idx’ is dependent on column ‘table’.

Mensagem 5074, Nível 16, Estado 1, Linha 1
The statistics ‘stat_1_table’ is dependent on column ‘table’.
Mensagem 5074, Nível 16, Estado 1, Linha 1
The statistics ‘stat_2_table’ is dependent on column ‘table’.

Bem, ai você ver o erro e pensa “Hummm!! Problema simples de resolver. Basta apenas pegar e executar a instrução de drop dos índices e estatísticas descritos, alterar o tipo de dados como pedido e criar novamente os objetos…” . Realmente essa seria a lógica para solução do problema, mas analisando “E se depois aparece a mesma demanda com número maior de objetos para dropar e recriar ou mais colunas para alterar o tipo de dados…”. Já ficaria mais trabalhoso. Então criei a Proc para facilitar meu trabalho.

/*

SQL Server Maintenance Solution – SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 e SQL Server 2016.

A solução é gratuita.

Última atualização: 10 de julho de 2020.

Créditos:
Jerfeson Santos (Consultor SQL Server)
jerfeson@consultiba.com.br
http://www.consultiba.com.br

*/

IF OBJECT_ID ( ‘dbo.uspDropCreateIndexStats’, ‘P’ ) IS NOT NULL
DROP PROCEDURE dbo.uspDropCreateIndexStats;
GO
CREATE PROCEDURE dbo.uspDropCreateIndexStats
@Esquema nvarchar(10),
@Tabela nvarchar(50),
@Coluna nvarchar(20)
AS

SET @esquema = @Esquema
SET @tabela = @Tabela
SET @coluna = @Coluna

CREATE TABLE #indexes
(
indexname  VARCHAR(255),
descricao  VARCHAR(255),
chavekey   VARCHAR(max),
includecol VARCHAR(max)
)

INSERT #indexes
EXEC Sp_helpindex2
@objname = @tabela

CREATE TABLE #stats
(
statname VARCHAR(255),
statkey  VARCHAR(max)
)

INSERT #stats
EXEC Sp_helpstats
@objname = @tabela

SELECT [indexname]             AS [INDICES],
‘DROP INDEX [‘ + indexname + ‘] ON [‘ + @esquema
+ ‘].[‘ + @tabela + ‘]’ AS [DROPAR],
CASE
WHEN includecol IS NOT NULL THEN
‘CREATE NONCLUSTERED INDEX [‘ + indexname
+ ‘] ON [‘ + @esquema + ‘].[‘ + @tabela +
‘] (‘
+ chavekey + ‘) include (‘ + includecol
+
‘) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]’
ELSE ‘CREATE NONCLUSTERED INDEX [‘ + indexname
+ ‘] ON [‘ + @esquema + ‘].[‘ + @tabela + ‘] (‘
+ chavekey
+
‘) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]’
END                     AS [RECRIAR]
FROM   #indexes
WHERE  ( chavekey LIKE ‘%’ + @coluna + ‘%’ )
OR ( includecol LIKE ‘%’ + @coluna + ‘%’ );

SELECT [statname]                                           AS [ESTATISTICAS],
‘DROP STATISTICS [‘ + @esquema + ‘].[‘ + @tabela
+ ‘].[‘ + statname + ‘] ‘                            AS [DROPAR],
‘CREATE STATISTICS [‘ + statname + ‘] ON [‘
+ @esquema + ‘].[‘ + @tabela + ‘] (‘ + statkey + ‘)’ AS [RECRIAR]
FROM   #stats
WHERE  ( statkey LIKE ‘%’ + @coluna + ‘%’ );

DROP TABLE #indexes
DROP TABLE #stats
GO

Fonte sp_helpindex2:  http://www.SQLskills.com/blogs/Kimberly.

Espero ter ajudado.

Grande abraço!