Bom dia, Comunidade. Good Morning Everyone!!!!!
Como diria aquele apresentador “O louco meu…..”, 2015 esta chegando ao seu final parece que foi ontem que publiquei o primeiro post deste ano, realmente o tempo esta passando de forma assustadora e não estamos dando conta do quanto estamos vivendo, ou melhor sobrevivendo as loucuras do mundo.
Mantendo a escrita e tradição não poderia deixar passar a oportunidade de compartilhar com vocês os mais novos Short Scripts que adicionei a minha biblioteca de Scripts dedicados ao Microsoft SQL Server desde a versão 2000. Neste momento minha modesta biblioteca de arquivos esta composta por 1.121 scripts organizados em 66 pastas, sub-dividos em 4 categorias:
- Comuns;
- Básicos;
- Intermediários; e
- Avançados.
Pois bem, no post de hoje destaco os seguintes recursos ou funcionalidades:
- Arquivo de ErrorLog e Logs Management;
- Contagem de linhas existentes em tabelas;
- CTE Recursiva;
- Diferença entre datas desconsiderando sábado e domingo;
- Extended Events Target;
- Função para formatar a primeira letra de cada palavra em maiúscula;
- Geração de combinação de letras;
- Multiple Server in Query Windows;
- Stored Procedure para gerar CNPJ e CPF;
- SQLCMD Mode; e
- Variável Table.
Fique a vontade para compartilhar, sugerir melhoras, críticas ou questionamentos sobre estes exemplos.
Segue abaixo a relação de short scripts:
1 – Exemplo – Utilizando Extended Events Target para separar e contar loings e logouts:
CREATE EVENT SESSION [CounterTest] ON SERVER
ADD EVENT sqlserver.login,
ADD EVENT sqlserver.logout(
ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.username))
ADD TARGET package0.event_counter
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
— login and logout a few times
GO
— query for data
— Query the Target
SELECT
n.value(‘@name[1]’, ‘varchar(50)’) AS Event
, n.value(‘@count[1]’, ‘int’) AS EventCounts
FROM
( SELECT
CAST(t.target_data AS XML) AS target_data
FROM
sys.dm_xe_sessions AS s
JOIN
sys.dm_xe_session_targets AS t
ON
t.event_session_address = s.address
WHERE
s.name = ‘CounterTest’
AND t.target_name = ‘event_counter’
) AS tab
CROSS APPLY target_data.nodes(‘CounterTarget/Packages/Package/Event’) AS q ( n )
GO
2 – Exemplo – Stored Procedure para gerar CNPJ e CPF:
CREATE PROCEDURE dbo.stpGerador_CPF_CNPJ (
@Quantidade INT = 1,
@Fl_Tipo BIT = 1
)
AS BEGIN
IF (OBJECT_ID(‘tempdb..#Tabela_Final’) IS NOT NULL) DROP TABLE #Tabela_Final
CREATE TABLE #Tabela_Final (
Nr_Documento VARCHAR(18)
)
DECLARE
@n INT,
@n1 INT,
@n2 INT,
@n3 INT,
@n4 INT,
@n5 INT,
@n6 INT,
@n7 INT,
@n8 INT,
@n9 INT,
@n10 INT,
@n11 INT,
@n12 INT,
@d1 INT,
@d2 INT
— CPF
IF (@Fl_Tipo = 0)
BEGIN
WHILE (@Quantidade > 0)
BEGIN
SET @n = 9
SET @n1 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n2 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n3 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n4 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n5 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n6 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n7 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n8 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n9 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @d1 = @n9 * 2 + @n8 * 3 + @n7 * 4 + @n6 * 5 + @n5 * 6 + @n4 * 7 + @n3 * 8 + @n2 * 9 + @n1 * 10
SET @d1 = 11 – ( @d1 % 11 )
IF ( @d1 >= 10 )
SET @d1 = 0
SET @d2 = @d1 * 2 + @n9 * 3 + @n8 * 4 + @n7 * 5 + @n6 * 6 + @n5 * 7 + @n4 * 8 + @n3 * 9 + @n2 * 10 + @n1 * 11
SET @d2 = 11 – ( @d2 % 11 )
IF ( @d2 >= 10 )
SET @d2 = 0
INSERT INTO #Tabela_Final
SELECT CAST(@n1 AS VARCHAR) + CAST(@n2 AS VARCHAR) + CAST(@n3 AS VARCHAR) + ‘.’ + CAST(@n4 AS VARCHAR) + CAST(@n5 AS VARCHAR) + CAST(@n6 AS VARCHAR) + ‘.’ + CAST(@n7 AS VARCHAR) + CAST(@n8 AS VARCHAR) + CAST(@n9 AS VARCHAR) + ‘-‘ + CAST(@d1 AS VARCHAR) + CAST(@d2 AS VARCHAR)
SET @Quantidade = @Quantidade – 1
END
END
— CNPJ
ELSE BEGIN
WHILE (@Quantidade > 0)
BEGIN
SET @n = 9
SET @n1 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n2 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n3 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n4 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n5 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n6 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n7 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n8 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n9 = 0
SET @n10 = 0
SET @n11 = 0
SET @n12 = 1
SET @d1 = @n12 * 2 + @n11 * 3 + @n10 * 4 + @n9 * 5 + @n8 * 6 + @n7 * 7 + @n6 * 8 + @n5 * 9 + @n4 * 2 + @n3 * 3 + @n2 * 4 + @n1 * 5
SET @d1 = 11 – ( @d1 % 11 )
IF (@d1 >= 10)
SET @d1 = 0
SET @d2 = @d1 * 2 + @n12 * 3 + @n11 * 4 + @n10 * 5 + @n9 * 6 + @n8 * 7 + @n7 * 8 + @n6 * 9 + @n5 * 2 + @n4 * 3 + @n3 * 4 + @n2 * 5 + @n1 * 6
SET @d2 = 11 – ( @d2 % 11 )
IF (@d2 >= 10)
SET @d2 = 0
INSERT INTO #Tabela_Final
SELECT ” + CAST(@n1 AS VARCHAR) + CAST (@n2 AS VARCHAR) + ‘.’ + CAST (@n3 AS VARCHAR) + CAST (@n4 AS VARCHAR) + CAST (@n5 AS VARCHAR) + ‘.’ + CAST (@n6 AS VARCHAR) + CAST (@n7 AS VARCHAR) + CAST (@n8 AS VARCHAR) + ‘/’ + CAST (@n9 AS VARCHAR) + CAST (@n10 AS VARCHAR) + CAST (@n11 AS VARCHAR) + CAST (@n12 AS VARCHAR) + ‘-‘ + CAST (@d1 AS VARCHAR) + CAST (@d2 AS VARCHAR);
SET @Quantidade = @Quantidade – 1
END
END
SELECT * FROM #Tabela_Final
END
3 – Exemplo – Reciclando o arquivo de ErrorLog e Logs Management:
EXEC sp_cycle_errorlog
GO
4 – Exemplo – Função – Formatar a primeira letra de cada palavra em maiúscula:
CREATE FUNCTION dbo.udfNomeProprio (
@Nome varchar(250)
)
RETURNS varchar(250)
AS
BEGIN
DECLARE @Pos tinyint = 1
DECLARE @Ret varchar(250) = ”
WHILE (@Pos < LEN(@Nome) + 1)
BEGIN
IF @Pos = 1
BEGIN
–FORMATA 1.LETRA DA “FRASE”
SET @Ret += UPPER(SUBSTRING(@Nome, @Pos, 1))
END
ELSE IF (SUBSTRING(@Nome, (@Pos-1), 1) = ‘ ‘
AND SUBSTRING(@Nome, (@Pos+2), 1) <> ‘ ‘) AND (@Pos+1) <> LEN(@Nome)
BEGIN
–FORMATA 1.LETRA DE “CADA INTERVALO””
SET @Ret += UPPER(SUBSTRING(@Nome, @Pos, 1))
END
ELSE
BEGIN
–FORMATA CADA LETRA RESTANTE
SET @Ret += LOWER(SUBSTRING(@Nome,@Pos, 1))
END
SET @Pos += 1
END
RETURN @Ret
END
GO
SELECT dbo.udfNomeProprio(‘pedro antonio galvão junior’)
GO
5 – Exemplo – Utilizando Multiple Server in Query Windows using SQLCMD Mode:
— Sem o GO o SQLCMD Mode não entende o final do bloco —
:connect saom4276
select @@SERVERNAME
:connect SAOM4276\SQLEXPRESS2014
select @@SERVERNAME
— Utilizando o comando Go para encerrar o bloco —
:connect saom4276
select @@SERVERNAME
Go
:connect SAOM4276\SQLEXPRESS2014
select @@SERVERNAME
Go
6 – Exemplo – Gerando combinação de letras:
create table #t (string varchar (2))
declare @a1 varchar(1), @a2 varchar(1)
set @a1=’A’
while @a1 <= ‘Z’
begin
set @a2=’A’
while @a2 <= ‘Z’
begin
insert into #t select @a1 + @a2
set @a2 = char (ascii(@a2) + 1)
end
set @a1 = char (ascii(@a1) + 1)
end
select string from #t
where string like ‘_’ /*single underscore*/
Go
7 – Exemplo – Calculando diferença entre datas desconsiderando sábado e domingo:
declare @Data datetime set @Data = ‘2015/10/01’
–Calcula a quantidade de dias entre a data inicial e a data atual, excluindo sbados e domingos soma 1 no fim pois no conta o proprio dia
WITH AllDates AS
( SELECT TOP (DATEDIFF(DAY, @Data, GETDATE()))
D = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.Object_ID), @Data)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
)
SELECT WeekDays = COUNT(*) +1 –
–Clcula a quantidade de feriados entre as datas
(select count(*) from FTAFE(NOLOCK)
where convert(datetime, convert(varchar, FTAFE.DT_FERIADO), 112) between @Data and GETDATE()
and FTAFE.CD_CIDADE in(0)
and DATEPART(weekday, convert(datetime, convert(varchar, FTAFE.DT_FERIADO), 112) ) not in(6,7))
FROM AllDates
WHERE DATEPART(WEEKDAY, D) NOT IN(6, 7)
Go
8 – Exemplo – Variável Table + CTE Recursiva para gerar sequência de letras:
declare @Tabela table
( COL1 INT, COL2 VARCHAR(1));
insert into @Tabela values
(75, NULL),
(78, ‘C’),
(12, ‘B’),
(24, ‘D’)
;with CTE_Rec (COL1, COL2) as
(
select
COL1,
COL2
from @Tabela
union all
select
COL1,
case when ASCII(COL2) > 66 then CAST(CHAR(ASCII(COL2) – 1) AS VARCHAR(1)) end
from CTE_Rec as c
where
COL2 is not null
)
select
COL1,
COL2
from CTE_Rec
order by
COL1,
COL2
Go
9 – Exemplo – Quantidade de Linhas – Todas as Tables:
— Exemplo 1 —
SELECT t.[name], p.[rows]
FROM sys.schemas s INNER JOIN sys.tables t
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i
ON i.[object_id] = t.[object_id]
AND i.[type] IN (0,1)
INNER JOIN sys.partitions p
ON p.[object_id] = t.[object_id]
AND p.[index_id] = i.[index_id]
Go
— Exemplo 2 —
;With Contador (name, rows)
As
(
SELECT t.[name], p.[rows]
FROM sys.schemas s INNER JOIN sys.tables t
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i
ON i.[object_id] = t.[object_id]
AND i.[type] IN (0,1)
INNER JOIN sys.partitions p
ON p.[object_id] = t.[object_id]
AND p.[index_id] = i.[index_id]
)
Select name, rows, ” as soma from Contador
Union all
Select ‘Total’ , Null, convert(varchar(10),sum(rows)) as soma from contador
Go
Caso você queria acessar os demais Short Scripts publicados em 2015, segue abaixo os links:
https://pedrogalvaojunior.wordpress.com/2015/10/21/short-scripts-outubro-2015/
https://pedrogalvaojunior.wordpress.com/2015/08/26/short-scripts-agosto-2015/
https://pedrogalvaojunior.wordpress.com/2015/06/03/short-script-junho-2015/
https://pedrogalvaojunior.wordpress.com/2015/05/04/short-script-maio-2015/
Mais uma vez obrigado por sua visita, espero que estes material posso ser útil e venha a colaborar em seu aprendizado.
Nos encontramos em breve…..