Short Scripts – Junho 2017


Boa noite galera, olá comunidade de banco de dados.

Tudo bem? Graças a deus mais um final de semana se aproximando, finalzinho de noite de quinta – feira, acredito que neste momento minha linda esposa esta curtindo mais um episódio de uma das suas séries favoritas, ou lendo mais um dos seus intermináveis livros (kkkkkk), isso não é um crítica, muito ao contrário, a leitura faz bem para a alma e principalmente para a mente, mas posso dizer que minha pequena Fernanda é uma degustadora insaciável de livros.

Mudando de assunto, este é o segundo post deste ano dedicado exclusivamente a sessão Short Scripts, sessão criada a alguns anos que  esta atraindo um número interessante de visitantes, principalmente de profissionais da área de banco de dados na busca por exemplos de códigos que possam solucionar ou elucidar na resolução de um determinado problema.

O post de hoje

Para este post, selecionei como de costume os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:

  • Check Constraint;
  • Common Table Expression;
  • Índices;
  • Information_schema.columns;
  • Monitoramento de Processos;
  • Network Protocol;
  • Operador Outer Apply;
  • Requisição de Disco;
  • Sys.dm_server_registry;
  • Sys.dm_tcp_listener_states;
  • sys.dm_exec_query_plan;
  • sys.dm_exec_sql_text;
  • sys.dm_exec_query_stats;
  • sys.types;
  • sys.tables;
  • sys.dm_db_index_usage_stats;
  • Sys.Identity_Columns; e
  • User Defined Function.

Então mãos no teclado, a seguir apresento os códigos e exemplos selecionados para o Short Script – Junho 2017. Vale ressaltar que todos os scripts publicados nesta sessão são devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.

Short Scripts

— Short Script 1  – Utilizando Check Constraint para cálculo de datas —

Create Table Alunos
(Codigo Int)
Go

— Adicionando a coluna e constraint —
Alter Table Alunos
Add DataNascimento DateTime
Constraint CK_Alunos_DataNascimento Check (DateDiff(Year,DataNascimento, GetDate()) >=18)
Go

— Adicionando somente a constraint —
Alter Table Alunos
Add Constraint CK_Alunos_DataNascimento
Check (DateDiff(Year,DataNascimento, GetDate()) >=18)
Go

— Default – Estado —
Alter Table Alunos
Add Constraint [DF_Estado] Default ‘SP’ for Estado
Go

— Short Script 2  – Aplicando o uso de Common Table Expression para inserir registros com valores de um registro anterior —

DECLARE @Metas TABLE ( Data DATE, Meta INT );

INSERT INTO @Metas
( Data, Meta )
VALUES ( DATEFROMPARTS(2017, 03, 29), 50 ),
( DATEFROMPARTS(2017, 04, 11), 35 ),
( DATEFROMPARTS(2017, 04, 13), 50 );

DECLARE @Producao TABLE
(
Data DATE ,
Quantidade INT
);

INSERT INTO @Producao
( Data, Quantidade )
VALUES ( DATEFROMPARTS(2017, 04, 10), 49 ),
( DATEFROMPARTS(2017, 04, 11), 35 ),
( DATEFROMPARTS(2017, 04, 12), 36 ),
( DATEFROMPARTS(2017, 04, 13), 50 ),
( DATEFROMPARTS(2017, 04, 14), 50 );

WITH DadosProduzidos
AS ( SELECT P.Data ,
Quantidade = SUM(P.Quantidade)
FROM @Producao AS P
GROUP BY P.Data
)
SELECT D.Data ,
[Produzido] = D.Quantidade ,
Meta = ( SELECT TOP 1 M.Meta
FROM @Metas AS M
WHERE M.Data <= D.Data
ORDER BY M.Data DESC
)
FROM DadosProduzidos D;

— Short Script 3  – Identificando a relação de todos os índices existentes em um banco de dados —

SELECT DB_NAME(Database_ID) DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(ius.OBJECT_ID) ObjName,
i.type_desc,
i.name,
user_seeks,
user_scans,
user_lookups,
user_updates
FROM sys.dm_db_index_usage_stats ius INNER JOIN sys.indexes i
ON i.index_id = ius.index_id
AND ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.tables t
ON t.OBJECT_ID = i.OBJECT_ID
GO

— Short Script 4 – Obtendo informações de network protocols e device através da sys.dm_server_registry —

SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Default Port’ FROM sys.dm_server_registry
WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’
AND value_name LIKE N’%TcpPort%’
AND CONVERT(float,value_data) > 0
Go

SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Dynamic Port ‘ FROM sys.dm_server_registry
WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’
AND value_name LIKE N’%TcpDynamicPort%’
AND CONVERT(float,value_data) > 0
Go

— Short Script 5 – Obtendo informações de network protocols e device através da sys.dm_tcp_listener_states —

SELECT port As ‘Default Port’ FROM sys.dm_tcp_listener_states
WHERE is_ipv4 = 1
AND [type] = 0
AND ip_address <> ‘127.0.0.1’
Go

— Short Script 6 – Criando uma User Defined Function com operador Outer Apply —

CREATE FUNCTION AttributesOfTable (@tableToSearch nvarchar(500))
returns table
return SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
from information_schema.columns
where TABLE_NAME = @tableToSearch;
go

Declare @tableToSearch table (nome_tabela varchar(50));
INSERT into @tableToSearch values (‘Customer’), (‘Order’), (‘Papagaio’);

SELECT T1.nome_tabela as [nome da tabela],
T2.TABLE_SCHEMA as [nome do esquema],
T2.COLUMN_NAME as [nome da coluna]
from @tableToSearch as T1
outer apply dbo.AttributesOfTable(T1.nome_tabela) as T2;

— Short Script 7 – Utilizando a DMV sys.identity_columns para identificar o valor identity de uma determinada coluna —

SELECT sys.tables.name AS [Table Name],
sys.identity_columns.name AS [Column Name],sys.types.name as Type,
last_value AS [Last Value]
FROM sys.identity_columns
INNER JOIN sys.tables
ON sys.identity_columns.object_id = sys.tables.object_id
Inner join sys.types on sys.types.user_type_id = sys.identity_columns.user_type_id
ORDER BY last_value DESC

— Short Script 8 – Consultando informações sobre o SQL Server armazenadas no Registro do Windows —

— A. Display the SQL Server services —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE key_name LIKE N’%ControlSet%’
Go

— B. Display the SQL Server Agent registry key values —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE key_name LIKE N’%SQLAgent%’
Go

— C. Display the current version of the instance of SQL Server —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE value_name = N’CurrentVersion’
Go

— D. Display the parameters passed to the instance of SQL Server during startup —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N’%Parameters’
Go

— E. Return network configuration information for the instance of SQL Server —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE keyname LIKE N’%SuperSocketNetLib%’
Go

— Short Script 9 – Identificando a relação de querys que apresentam o maior consumo de disco durante seu período de processamento – 

SELECT TOP 20 SUBSTRING(qt.text,
(qs.statement_start_offset/2)+1,
((CASE
qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE
qs.statement_end_offset
END –
qs.statement_start_offset)/2)+1),
qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads,
qs.max_logical_reads,
qs.total_elapsed_time,
qs.last_elapsed_time,
qs.min_elapsed_time,
qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats
qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY
qs.total_logical_reads DESC

Legal, mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância e apresentam um valor e conhecimento do mais alto nível.


Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são bastante conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

Agradecimento

Agradeço sua visita, com certeza é imenso prazer saber que você teve interesse em acessar este post.

Nos encontramos novamente no mês de setembro, para o próximo post dedicado a sessão Short Scripts.

Um grande abraço, boa noite, bom descanso.

Valeu.

Short Scripts – Março 2017


Hoje é sexta – feira, sexta – feira…

Salve, salve comunidade e amantes de bancos de dados e SQL Server, Tudo bem? Mais uma final de semana próximo após uma longa semana de muito trabalho, se eu for falar de muito trabalho, sinceramente  esta semana foi complicada, repleta de novidades e muita troca de conhecimento.

Conforme o prometido no final de 2016, estou retornando com o primeiro post da sessão Short Scripts, sessão criada a alguns anos no meu blog que lentamente esta conseguindo ajudar diversos profissionais da área de banco de dados na busca por exemplos de códigos que possam solucionar ou elucidar na resolução de um determinado problema.

O post de hoje

Como já destaquei acima, este é o primeiro post de 2017 dedicado exclusivamente a sessão Short Scripts, na relação de scritps selecionados para hoje, você vai poder encontrar códigos relacionados com os seguintes assuntos:

  • Auditoria,
  • Comando Intersect,
  • Comando OpenQuery,
  • Comando Order By,
  • Datatype Char,
  • Datatype Int,
  • DMF Sys.dm_exec_sessions,
  • DMV Sys.system_internals_partitions,
  • DMV Sys.system_internals_allocation_units,
  • Função Substring,
  • Índices,
  • Informações sobre conexão de usuário,
  • Páginas de Dados,
  • Trigger, e
  • Variáveis.

Então mãos no teclado, a seguir apresento os códigos e exemplos selecionados para o Short Script – Março 2017. Vale ressaltar que todos os scripts publicados nesta sessão são devidamente testados antes de serem publicados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas. Fique a vontade para compartilhar, comentar, melhorar cada um destes códigos.

Short Scripts

— Short Script 1 – Realizando Auditoria in Live —

SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END –
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50              — Ignore system spids.
AND session_Id NOT IN (@@SPID)     — Ignore this current statement.
ORDER BY 1, 2
Go

— Short Script 2 – Utilizando comando Intersect com Datatypes Char e Int —

Create Table #A (x Char(2));
Go
Insert Into #A Values (‘1’);
Insert Into #A Values (‘6’);
Insert Into #A Values (‘2’);
Insert Into #A Values (‘3’);
Insert Into #A Values (‘5’);
Insert Into #A Values (‘5’);
Insert Into #A Values (‘6’);
Insert Into #A Values (‘9’);
Go
Create Table #B (M BigInt);
Go
Insert Into #B Values(5);
Insert Into #B Values(5);
Insert Into #B Values(6);
Insert Into #B Values(7);
Insert Into #B Values(7);
Go
— (Select #1)
SELECT x AS ‘Select #1’ FROM #A
INTERSECT SELECT M FROM #B
Go
— (Select #2)
SELECT DISTINCT(x) AS ‘Select #2’
FROM #A LEFT OUTER JOIN #B
ON #A.x = #B.M
Go
— (Select #3)
SELECT DISTINCT(x) AS ‘Select #3’
FROM #A LEFT OUTER JOIN #B
ON #A.x = #B.M
Go
— (Select #4)
SELECT DISTINCT(x) AS ‘Select #4’
FROM #A INNER JOIN #B
ON #A.x = #B.M
Go
— (Select #5)
SELECT x AS ‘Select #5’
FROM #A INNER JOIN #B
ON #A.x = #B.M
Go
— Short Script 3 – Utilizando comando OpenQuery com variáveis —
— Valores Básicos —
DECLARE @TSQL varchar(8000), @VAR char(2)
SELECT  @VAR = ‘teste’
SELECT  @TSQL = ‘SELECT * FROM OPENQUERY(MeuLinkedServer,”SELECT * FROM MinhaTabela WHERE User = ””’ + @VAR + ”””’)’
EXEC (@TSQL)
Go
— Query Complexa —
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = ‘MyLinkedServer’
SET @OPENQUERY = ‘SELECT * FROM OPENQUERY(‘+ @LinkedServer + ‘,”’
SET @TSQL = ‘SELECT au_lname, au_id FROM pubs..authors”)’
EXEC (@OPENQUERY+@TSQL)
— Use o Sp_executesql procedimento armazenado —
DECLARE @VAR char(2)
SELECT  @VAR = ‘CA’
EXEC MyLinkedServer.master.dbo.sp_executesql
N’SELECT * FROM pubs.dbo.authors WHERE state = @state’,
N’@state char(2)’,
@VAR
Go
— Short Script 4 – Realizando order by com base na função SubString —
Declare @Tabela Table
(Codigo VarChar(15))
Insert Into @Tabela Values(‘191-XXX-003’)
Insert Into @Tabela Values(‘192-XXX-003’)
Insert Into @Tabela Values(‘193-XXX-003’)
Insert Into @Tabela Values(‘194-XXX-003’)
Insert Into @Tabela Values(‘195-XXX-003’)
Insert Into @Tabela Values(‘191-XXX-001’)
Insert Into @Tabela Values(‘192-XXX-001’)
Insert Into @Tabela Values(‘193-XXX-001’)
Insert Into @Tabela Values(‘194-XXX-001’)
Insert Into @Tabela Values(‘195-XXX-001’)
Insert Into @Tabela Values(‘191-XXX-002’)
Insert Into @Tabela Values(‘192-XXX-002’)
Insert Into @Tabela Values(‘193-XXX-002’)
Insert Into @Tabela Values(‘194-XXX-002’)
Insert Into @Tabela Values(‘195-XXX-002’)
Select * from @Tabela
Order By SubString(codigo,Len(Codigo)-2,3) Asc
Go
— Short Script 5 – Monitorando querys em execução —
SELECT
DES.SESSION_ID,
DES.CPU_TIME,
DES.READS,
DES.WRITES,
DES.LOGICAL_READS,
DES.ROW_COUNT,
DER.SESSION_ID,
DES.STATUS,
DES.HOST_NAME,
DES.PROGRAM_NAME,
DES.LOGIN_NAME,
DES.ORIGINAL_LOGIN_NAME,
DEC.CLIENT_NET_ADDRESS,
DEC.AUTH_SCHEME,
DEC.NET_TRANSPORT,
SUBSTRING(T.[TEXT], DER.[STATEMENT_START_OFFSET] / 2,
COALESCE(NULLIF(DER.[STATEMENT_END_OFFSET], – 1) / 2, 2147483647)) AS COMANDO
FROM
SYS.DM_EXEC_SESSIONS AS DES
INNER JOIN SYS.DM_EXEC_REQUESTS DER
ON DER.BLOCKING_SESSION_ID = DES.SESSION_ID
INNER JOIN SYS.DM_EXEC_CONNECTIONS DEC
ON DEC.SESSION_ID = DES.SESSION_ID
INNER JOIN SYS.DM_EXEC_REQUESTS DER2
ON DER2.SESSION_ID = DES.SESSION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(DER.[SQL_HANDLE]) AS T
GO
— Short Script 6 – Identificando tabelas e suas respectivas páginas de dados —
— Exemplo 1: —
SELECT P.partition_id,
OBJECT_NAME(P.object_id) As ObjectName,
U.allocation_unit_id,
SU.First_Page,
SU.Root_Page,
SU.First_IAM_Page
From Sys.Partitions As P INNER JOIN Sys.Allocation_Units As U
ON P.hobt_id = U.container_id
Inner Join Sys.system_internals_allocation_units SU
On u.allocation_unit_id = su.allocation_unit_id
Go
— Exemplo 2: —
SELECT SIP.partition_id,
OBJECT_NAME(SIP.object_id) As ObjectName,
sip.rows,
SU.First_Page,
SU.Root_Page,
SU.First_IAM_Page
From Sys.system_internals_partitions As SIP Inner Join Sys.system_internals_allocation_units SU
On sip.partition_id = su.allocation_unit_id
Go
— Short Script 7 – Identificando índices com Escrita Excessiva —
— Quantidade de Índices com Escrita Excessiva em comparação a leitura —
SELECT  OBJECT_NAME(s.object_id),
i.name,
i.type_desc
FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK ) Inner JOIN sys.indexes i WITH (NOLOCK)
ON s.index_id = i.index_id
AND s.object_id = i.object_id
WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1
AND s.database_id = DB_ID()
AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )
AND s.index_id > 1
Go
— Quantidade de Índices com Escrita Excessiva —
SELECT  COUNT(*)
FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK )
WHERE   OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1
AND s.database_id = DB_ID()
AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )
AND s.index_id > 1
Go
— Short Script 8 – Identificando o espaço ocupado por conexões em uso —
SELECT A.session_id,
B.host_name, B.Login_Name ,
(user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 as TotalalocadoMB,
D.Text
FROM sys.dm_db_session_space_usage A Inner JOIN sys.dm_exec_sessions B
ON A.session_id = B.session_id
Inner JOIN sys.dm_exec_connections C
ON C.session_id = B.session_id
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) As D
WHERE A.session_id > 50
and (user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 > 100 — Ocupam mais de 100 MB
ORDER BY totalalocadoMB desc
COMPUTE sum((user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128)
Go
— Short Script 9 – Obtendo a diferença de datas entre registro anterior e o próximo registro —
Create Table Datas
(ID Int Identity(1,1),
DataInicio Date,
DataFinal Date)
Go
Insert Into Datas Values (GETDATE(), GETDATE()+30)
Insert Into Datas Values (GETDATE()+1, GETDATE()+20)
Insert Into Datas Values (GETDATE()+2, GETDATE()+10)
Insert Into Datas Values (GETDATE(), GETDATE()+5)
Go
— Exemplo 1 —
Select I.ID, I.DataInicio, F.DataFinal,
DateDiff(D, I.DataInicio, F.DataFinal) As Intervalo
From Datas I Left Join Datas F
On I.ID = F.ID + 1

Go

— Exemplo 2 —
SELECT
[current].Id,
[current].Time CurrentValue,
[next].Time          NextValue
FROM #temp AS [current] LEFT JOIN #temp AS [next]
ON [next].Id = (SELECT MIN(Id) FROM #temp

                                 WHERE Id > [current].Id)
Go
— Short Script 10 – Criando um trigger condicional —
–Criando a Table de Novos Produtos–
Create Table NovosProdutos
(Codigo Int Identity(1,1),
Descricao VarChar(10))
–Criando a Table de Histórico Novos Produtos–
Create Table HistoricoNovosProdutos
(Codigo Int,
Descricao VarChar(10))
Go
–Inserindo valores —
Insert Into Novosprodutos Values(‘Arroz’)
Insert Into Novosprodutos Values(‘Arroz1’)
Insert Into Novosprodutos Values(‘Arroz2’)
Insert Into Novosprodutos Values(‘Arroz3’)
Go
–Criando a Trigger para controle de histórico–
Create TRIGGER T_Historico
ON NovosProdutos
for update
AS
IF (Select Descricao from Inserted) <> (Select Descricao from Deleted)
BEGIN
INSERT Into HistoricoNovosProdutos (Codigo, Descricao)
SELECT Codigo, Descricao FROM INSERTED
END
Go
–Fazendo os teste —
Update NovosProdutos
Set Descricao=’Arroz 4′
Where Codigo = 1
Go
Update NovosProdutos
Set Descricao=’Arroz1′
Where Codigo = 2
Go
Select * from NovosProdutos
Go

Muito bem, mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância e apresentam um valor e conhecimento do mais alto nível.


Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são bastante conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

Agradecimento

Mais uma vez obrigado por sua visita, um forte abraço…

Nos encontramos em breve nos próximos posts desta e outras sessões do meu blog, valeu.

Short Scripts – Dezembro 2016


Buenas tardes….. Comunidade.

Hoje um dia mais que especial na minha vida e da minha família, meu pai Pedro Galvão esta completando 60 anos de vida, meu deus, como o tempo nos supreende com a sua velocidade.

Quero aproveitar este post para agradeço por este privilégio de poder conviver com uma pessoa tão integra, humilde, trabalhadora, honesta, enfim existem milhares de adjetivos para definir o que meu pai e sua excência em pessoa representa.

O post de hoje

Falando do post de hoje, este é um último post dedicado a sessão Short Scripts no ano de 2016, mas com certeza em 2017 vai estar retornando com toda a força.

Como de costume, e não poderia ser diferente, estamos no final do ano, próximos ao Natal, momento de festa e troca de presentes, lógicamente vou deixar o meu presentinho para vocês, compartilhando os últimos scripts adquiridos neste ano, relacionados aos seguintes assuntos:

  • Calendário anual de feriados;
  • Conversão de Binários para String;
  • CTE e CTE Recursiva;
  • Funções;
  • Extended Stored Procedure XP_ServiceControl;
  • MaxRecursion;
  • Union e Union All; e
  • Monitoramento de Serviços.

Short Scripts

A seguir apresento a relação de Short Scripts de hoje:

— Short Script 1 – Monitorando os serviços do SQL Server através da Extended Stored Procedure – XP_ServiceControl

Set NoCount On
CREATE TABLE #ServicesStatus
(
myid int identity(1,1),
serverName nvarchar(100) default @@serverName,
serviceName varchar(100),
Status varchar(50),
checkdatetime datetime default (getdate())
)
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N’QUERYSTATE’,N’MSSQLServer’
update #ServicesStatus set serviceName = ‘MSSQLServer’ where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N’QUERYSTATE’,N’SQLServerAGENT’
update #ServicesStatus set serviceName = ‘SQLServerAGENT’ where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N’QUERYSTATE’,N’msdtc’;
update #ServicesStatus set serviceName = ‘msdtc’ where myid = @@identity;
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N’QUERYSTATE’,N’sqlbrowser’
update #ServicesStatus set serviceName = ‘sqlbrowser’ where myid = @@identity
Select * from #ServicesStatus
— Short Script 2 – Realizando a conversão de Binário para String e String para Binário —
— Declarando uma chave legível —
DECLARE @chave  VARCHAR(MAX) =’quechavemalfeita’
— Convertendo para Binário —
DECLARE @dadosBinary VARBINARY(MAX) = CONVERT(VARBINARY(MAX),@chave,0)
SELECT @chave,@dadosBinary
— Observando as diferenças —
SELECT CONVERT(VARCHAR(MAX),@dadosBinary,0) –o Tipo 0 tranforma em texto legivelSELECT CONVERT(VARCHAR(MAX),@dadosBinary,1) — transforma o valor BINARY EM VALOR VARCHAR DEIXANDO O MESMO CONTEUDO

SELECT CONVERT(VARCHAR(MAX),@dadosBinary,2)– transforma o valor BINARY EM VALOR VARCHAR RETIRANDO O 0x NO INICIO
Go

— Short Script 3 – Criando um calendário anual para feriados —
CREATE SCHEMA Calendar
Go
CREATE FUNCTION Calendar.Computus (@Y INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @a INT, @b INT, @c INT, @d INT, @e INT, @f INT, @g INT, @h INT, @i INT, @k INT, @L INT, @m INT
SET @a = @Y % 19
SET @b = @Y / 100
SET @c = @Y % 100
SET @d = @b / 4
SET @e = @b % 4
SET @f = (@b + 8) / 25
SET @g = (@b – @f + 1) / 3
SET @h = (19 * @a + @b – @d – @g + 15) % 30
SET @i = @c / 4
SET @k = @c % 4
SET @L = (32 + 2 * @e + 2 * @i – @h – @k) % 7
SET @m = (@a + 11 * @h + 22 * @L) / 451
RETURN(DATEADD(month, ((@h + @L – 7 * @m + 114) / 31)-1, cast(cast(@Y AS VARCHAR) AS Datetime)) + ((@h + @L – 7 * @m + 114) % 31))
END
GO
CREATE TABLE Calendar.[Calendar] (
[Date] datetime NOT NULL,
[Year] int NOT NULL,
[Quarter] int NOT NULL,
[Month] int NOT NULL,
[Week] int NOT NULL,
[Day] int NOT NULL,
[DayOfYear] int NOT NULL,
[Weekday] int NOT NULL,
[Fiscal_Year] int NOT NULL,
[Fiscal_Quarter] int NOT NULL,
[Fiscal_Month] int NOT NULL,
[KindOfDay] varchar(10) NOT NULL,
[Description] varchar(50) NULL,
PRIMARY KEY CLUSTERED ([Date])
)
GO
ALTER TABLE Calendar.[Calendar]
ADD CONSTRAINT [Calendar_ck] CHECK (  ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week]  BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN (‘HOLIDAY’, ‘SATURDAY’, ‘SUNDAY’, ‘BANKDAY’)))
GO
SET DATEFIRST 1;
WITH Dates(Date)
AS
(
SELECT cast(‘1999’ AS DateTime) Date
UNION ALL
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast(‘2021’ AS DateTime) -1
),
DatesAndThursdayInWeek(Date, Thursday)
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date – 1
WHEN 6 THEN Date – 2
WHEN 7 THEN Date – 3
END AS Thursday
FROM Dates
),
Weeks(Week, Thursday)
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week,
Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO Auxiliary.Calendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,
YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,
CASE
WHEN (DATEPART(DayOfYear, d.Date) = 1)          — New Year’s Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))-7)  — Palm Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-3)  — Maundy Thursday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-2)  — Good Friday
OR (d.Date = Auxiliary.Computus(YEAR(Date)))    — Easter Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) — Ascension Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) — Pentecost
OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) — Whitmonday
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      — Labour day
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     — Constitution day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    — Cristmas day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    — Boxing day
THEN ‘HOLIDAY’
WHEN DATEPART(Weekday, d.Date) = 6 THEN ‘SATURDAY’
WHEN DATEPART(Weekday, d.Date) = 7 THEN ‘SUNDAY’
ELSE ‘BANKDAY’
END KindOfDay,
CASE
WHEN (DATEPART(DayOfYear, d.Date) = 1)            THEN ‘New Year”s Day’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-7)  THEN ‘Palm Sunday’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-3)  THEN ‘Maundy Thursday’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-2)  THEN ‘Good Friday’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date)))    THEN ‘Easter Sunday’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN ‘Ascension Day’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN ‘Pentecost’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN ‘Whitmonday’
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      THEN ‘Labour day’
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     THEN ‘Constitution day’
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    THEN ‘Cristmas day’
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    THEN ‘Boxing day’
END Description
FROM DatesAndThursdayInWeek d
inner join Weeks w
on d.Thursday = w.Thursday
OPTION(MAXRECURSION 0)
GO
CREATE FUNCTION Auxiliary.Numbers (@AFrom INT, @ATo INT, @AIncrement INT)
RETURNS @RetNumbers TABLE
([Number] int PRIMARY KEY NOT NULL)
AS
BEGIN
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
INSERT @RetNumbers
SELECT n from Numbers
OPTION(MAXRECURSION 0)
RETURN;
END
GO
CREATE FUNCTION Auxiliary.iNumbers( @AFrom INT, @ATo INT, @AIncrement INT)
RETURNS TABLE
AS
RETURN( WITH Numbers(n)
AS
(SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo)
SELECT n AS Number from Numbers
)
GO

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

Agradecimento

Mais uma vez obrigado por sua visita, um forte abraço…

Feliz Natal com muita saúde, paz, alegria e esperança. Que 2017 você possa brilhar ainda mais e estar junto com a comunidade.

Short Scripts – Outubro 2016


Muito bom dia!!

Olá comunidade, estou retornando com um pouco de atraso, mas não se esquecendo das responsabilidades, caso você não tenha entendido estou me referindo ao atraso em publicar mais um post dedicado a sessão Short Scripts que deveria ter ocorrido no mês de setembro.

No decorrer de 2016 fui realizando algumas mudanças nas principais sessões do meu blog e alterando o calendário de publicação o que refletiu na alteração das publicações relacioandas as sessões: Material de Apoio e Short Scripts.

O post de hoje

Falando deste post, vou compartilhar com vocês short scripts dedicados exclusivamente ao novo Microsoft SQL Server, estou me referindo a versão 2016 lançada em Junho deste ano.

Dentre algumas novidades a Microsoft implmentou diversas inovações e melhorias na linguagem Transact-SQL, entre as quais destaco:

  • AT TIME Zone;

  • Data Masking;

  • DMV Sys.dm_exec_function_stats;

  • Função String_Split();

  • Json;

  • Temporal Table;

  • Truncate Table With Partition; e

  • View Sys.Time_Zone_Info.

Short Scripts

A seguir apresento a relação de Short Scripts de hoje:

— Short Script 1 – Utilizando a instrução AT TIME Zone —
SELECT SalesOrderID,
OrderDate,
OrderDate AT TIME ZONE ‘Pacific Standard Time’ AS   OrderDate_TimeZonePST
FROM Sales.SalesOrderHeader
Go

— Short Script 2 – Aplicando mascaramento de dados com DataMasking —
CREATE TABLE DDM_Questions
(myemail VARCHAR(300) MASKED WITH (FUNCTION=’email()’))
GO

INSERT dbo.DDM_Questions (myemail)
VALUES (‘@dog.com’);
GO
SELECT myemail FROM dbo.DDM_Questions;
GO
— Short Script 3 – Utilizando a DMV sys.dm_exec_function_stats —

USE AdventureWorks2016
GO
Select  ProductID,
LocationID,
Shelf,
Bin,
Quantity,
ModifiedDate,
dbo.ufnGetStock(ProductInventory.ProductID)  As ‘Stock’,
dbo.ufnGetProductDealerPrice(ProductInventory.ProductID,
GETDATE()) As ‘Dealer Price’,
dbo.ufnGetProductStandardCost(ProductInventory.ProductID,
GETDATE()-100) As ‘Standard Cost’
From Production.ProductInventory
Go
SELECT Concat(DB_NAME(database_id), ‘.’ ,  OBJECT_SCHEMA_NAME(OBJECT_ID, database_id), ‘.’,  OBJECT_NAME(OBJECT_ID, database_id)) AS Function_Name,
QS.last_execution_time,
QS.max_worker_time,
QS.max_physical_reads,
QS.max_logical_reads,
QS.max_logical_writes,
T.Text
FROM sys.dm_exec_function_stats QS CROSS APPLY
sys.dm_exec_sql_text(sql_handle) T
Where  database_id = DB_ID()
Order by last_execution_time
Go
— Short Script 4 – Realizando spliting de dados com a função String_Split() —
— Exemplo 1 – Separando de forma simples uma string –
SELECT *
FROM STRING_SPLIT(‘Junior,Galvão,MVP,SQL Server’,’,’)
Go
— Exemplo 2 – Fazendo uso de variáveis como parâmetros de entrada de valores –
DECLARE @string VARCHAR(100) = ‘Microsoft,SQL Server,2016,RC0′,
@separador CHAR(1) =’,’
SELECT *
FROM STRING_SPLIT(@string,@separador)
Go
— Exemplo 3 – Armazenando o resultado da divisão de uma string em uma nova tabela –
DECLARE @string VARCHAR(100) = ‘Microsoft,SQL Server,2016,RC0′,
@separador CHAR(1) =’,’
SELECT * INTO #SplitTable
FROM STRING_SPLIT(@string,@separador)
GO

 

— Short Script 5 – Gerando dados no formato Json —
— Exemplo 1 – Utilizando a claúsula JSON Auto —
Select Top 20
AddressID, AddressLine1,
City, PostalCode,
ModifiedDate
from [Person].[Address]
For JSON Auto
Go

— Exemplo 2 – Utilizando a claúsula JSON Path —
Select Top 5
AddressID, AddressLine1,
City, PostalCode,
ModifiedDate
from [Person].[Address]
For JSON Path
Go
— Exemplo 3 – Utilizando a claúsula JSON Path em conjunto com uma variável –
DECLARE @MyJson as NVARCHAR(MAX)
SET @MyJson = (SELECT ‘Pedro’ as Nome, ‘Galvão’ as Sobrenome,
35 as Idade, Getdate() as DataAtual
FOR JSON PATH)
Print (@MyJson)

Go

— Short Script 6 – Criando uma nova Temporal Table —
CREATE TABLE [dbo].[Orders](
[OrdersID] int PRIMARY KEY CLUSTERED,
[Quantity] int NOT NULL,
[UnitPrice] money not null,
[OrderDate] datetime2 NOT NULL,
[SysStartTime] datetime2(0) GENERATED ALWAYS AS ROW START NOT NULL,
[SysEndTime] datetime2(0) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME ([SysStartTime],  [SysEndTime]))
WITH (SYSTEM_VERSIONING = ON )
Go

— Short Script 7 – Realizando Truncate Table With Partition —
— Passo 1 – Criando uma nova Partition Function —
CREATE PARTITION FUNCTION [PFRegistro] (int)
AS RANGE RIGHT FOR VALUES
(10000, 30000,
50000, 70000,
90000);
Go

— Passo 2 – Criando um novo Partition Schema —
CREATE PARTITION SCHEME [PSRegistro]
AS PARTITION [PFRegistro]
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);— Passo 3 – Criando a tabela TabelaParticionada —
CREATE TABLE dbo.TabelaParticionada
(NumRegistro INT NOT  NULL,
Dados char(1000) NULL,
DataCadastro datetime NOT NULL)
ON [PSRegistro](NumRegistro)
GO— Passo 4 – Populando a Tabela – TabelaParticionada —
INSERT dbo.TabelaParticionada
SELECT TOP 10000
s1.number * 1000  +  s2.number * 100 + s1.number  As NumRegistro,
Replicate(‘SQL Server 2016 ‘,S2.number+1) As  Dados,
GETDATE()+S2.number As DataCadastro
FROM master..spt_values s1 CROSS JOIN master..spt_values s2
WHERE s1.number BETWEEN 0 AND 999 AND s1.type = ‘P’
AND s2.number BETWEEN 0 AND 99 AND s2.type = ‘P’
— Passo 5 – Obtendo a lista de Partitions criadas para TabelaParticionada —
SELECT
$PARTITION.[PFRegistro] (NumRegistro) AS ‘Partition’,
COUNT(*) AS TotalRegistros,
MIN(NumRegistro) AS RegistroInicial,
MAX(NumRegistro) AS RegistroFinal
FROM dbo.TabelaParticionada
GROUP BY $PARTITION.[PFRegistro] (NumRegistro)
Go
— Passo 6 – Realizando a exclusao da particao 1 e tambem da particao 4 ate particao 6 —
TRUNCATE TABLE dbo.TabelaParticionada
WITH (PARTITIONS (2, 4 TO 6));
Go

 

— Short Script 8 – Consultando a relação de TIME Zone —
Select Name,
    Current_UTC_OffSet,
Is_Currently_DST
From Sys.Time_Zone_Info
Go


Bom pessoal, chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar e ilustrar algumas das novidades adicionados ao Microsoft SQL Server 2016.

Acredito que você tenha observado que estes códigos já são bastante conhecidos no meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server 2016, publicados no decorrer deste ano.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

Agradecimento

Mais uma vez obrigado por sua visita, um forte abraço, nos encontramos em breve.

Short Scripts – Junho 2016


Salve, salve minha gente, boa tarde!!!

Mais uma segunda – feira começando e mais uma semana se iniciando, já passamos da metade do mês de Junho tão aguardado devido ao lançamento do novo Microsoft SQL Server 2016. Caso você tenha perdido alguma informação sobre este lançamento, aproveito para compartilhar aqui um dos diversos posts publicados no meu blog sobre esta nova versão:

Voltando a falar sobre o Short Scripts, esta é uma das sessões mas visitadas do meu blog, onde o objetivo  é compartilhar os scripts existentes em minha biblioteca de códigos  dedicados exclusivamente para o SQL Server. Muitos destes scripts são resultado de pesquisas, posts publicados nos fóruns MSDN e também de respostas encontradas em outros websites de profissionais, administradores de banco de dados, professores e comunidades.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso. Todos os scripts publicados nesta sessão são devidamente testados antes de serem publicados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Caso você não tenha acessado os últimos posts, não perca tempo, utilize os links publicados a seguir:

https://pedrogalvaojunior.wordpress.com/2016/03/22/short-scripts-marco-2016/

https://pedrogalvaojunior.wordpress.com/2015/12/14/short-script-dezembro-2015/

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/

Para esta relação você vai encontrar scripts relacionados aos seguintes assuntos, conceitos, recursos ou funcionalidades:

  • Acesso a banco de dados;
  • Collation;
  • Comando Exec;
  • Comando Union;
  • Comando Union All;
  • Comando Create Table;
  • Comando Alter Table;
  • Common Table Expression;
  • DBCC CheckPrimaryFile;
  • DMV sys.dm_db_index_usage_stats;
  • DMV sys.dm_os_buffer_descriptors;
  • Índices Clustered e NonClustered;
  • Option MaxRecursion;
  • Plano de Execução;
  • Querys consideradas pesadas;
  • Tabela de sistema sys.allocation_units;
  • Tabela de sistema sys.partitions;
  • Tabela de sistema sys.indexes;
  • Tabela e caracteres Unicode; e
  • Recursividade.
A seguir, apresento a relação de short scripts:

— Short Script 1 – DBCC CheckPrimaryFile Verificando se o arquivo realmente é um Primary File —
DBCC CheckPrimaryFile (‘C:\Bancos\Laboratorio.MDF’,0)
Go

 

— Short Script 2 – DBCC CheckPrimaryFile – Retornando o conjunto completo de informações associados ao Primary File que formam a estrutura do respectivo Banco de Dados —
DBCC CheckPrimaryFile (‘C:\Bancos\Laboratorio.MDF’,1)
Go

 

— Short Script 3 – DBCC CheckPrimaryFile – Retornando o nome do banco de dados, versão e collation relacionados ao Primary File —
DBCC CheckPrimaryFile (‘C:\Bancos\Laboratorio.MDF’,2)
Go

— Short Script 4 – DBCC CheckPrimaryFile – Retornando o status, fileid, name e filename associados ao Primary File que formam a estrutura do respectivo Banco de Dados —
DBCC CheckPrimaryFile (‘C:\Bancos\Laboratorio.MDF’,3)
Go
— Short Script 5 – Informações sobre acesso ao Banco de Dados —
WITH agg AS
(SELECT last_user_seek,
                  last_user_scan,
                  last_user_lookup,
last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID()
)
SELECT last_read = MAX(last_read),
                 last_write = MAX(last_write)
FROM
(SELECT last_user_seek, NULL FROM agg
UNION ALL
SELECT last_user_scan, NULL FROM agg
UNION ALL
SELECT last_user_lookup, NULL FROM agg
UNION ALL
SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write);
Go

 

— Short Script 6 – Observando a mudança de comportamento após a troca de Collation —

CREATE TABLE [dbo].[Authors]
([id] [INT] NULL,
   [author] [VARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [dateposted] [DATE] NULL) ON [PRIMARY]
GO
INSERT Authors  VALUES (1, ‘Steve’, ‘20160101’),
                                                   (2, ‘STEVE’, ‘20160201’),
                                                   (3, ‘Andy’, ‘20160301’),
                                                   (4, ‘andy’, ‘20160401’)
GO
CREATE PROCEDURE GetAuthors @author VARCHAR(50)
AS
BEGIN
SELECT a.id, a.author FROM dbo.Authors a
WHERE a.author = @author
END
GO
— If I run the procedure with a parameter of ‘Steve’, it returns two rows. I then run this code:
Exec GetAuthors ‘Steve’
Go
ALTER TABLE dbo.Authors
ALTER COLUMN author VARCHAR(50) COLLATE SQL_Latin1_General_CP437_BIN2 NULL
— If I were to execute the stored procedure, what would happen?
Exec GetAuthors ‘Steve’
Go

 

— Short Script 7 – Descobrindo o código Unicode de um caracter ou String —

— Exemplo 1 —
DECLARE @n CHAR(10);
SET @n = N’Abc’;
SELECT UNICODE(@n);
Go
— Exemplo 2 —
DECLARE @n NCHAR(10);
SET @n = N’??????????’;
SELECT UNICODE(@n);
Go

 

— Short Script 8 – Criando CTEs —

— Exemplo 1 – Criando uma simples CTE —
;With Exemplo1(Valor, Nome)
As
(
Select 1, ‘Pedro Galvão’ As Nome
)
Select * from Exemplo1
Go
— Exemplo 2 – Criando uma CTE com Union de Selects —
;With Exemplo2(Valor)
As
( Select 10
Union
Select 50
Union
Select 8
Union
Select 10 + 2
)
Select Valor = (Select Max(valor) From Exemplo2) + (Select Sum(Valor) From Exemplo2)
Go
– Short Script 9 – Criando CTEs com Recursividade —
— Exemplo – Criando uma nova CTE Recursiva concatenando dados —
;With ConcatenarNomes(nome)
AS
( SELECT Nome = CONVERT(Varchar(4000),’Pedro Antonio’)
UNION ALL
SELECT CONVERT(Varchar(4000),nome + ‘ Galvão Junior’) FROM ConcatenarNomes
WHERE LEN(nome) < 30
)
SELECT Nome FROM ConcatenarNomes
Go
— Exemplo 2 – Criando uma CTE com Union + Recursividade – Simulando uma sequência de números pares —
;With CTENumerosPares(Numero)
As
( Select 0 As Numero
Union All
Select Numero + 2 As Numero From CTENumerosPares
Where Numero < 100
)
Select Numero From CTENumerosPares
Go
— Exemplo 3 – Criando uma CTE com Union + Recursividade – Simulando uma sequência de números —
;With CTENumerosSequenciais(Numero)
AS
(   SELECT 1 AS Numero
UNION ALL
SELECT Numero + 1 AS num FROM CTENumerosSequenciais
WHERE Numero < 1000
)
SELECT * FROM CTENumerosSequenciais
OPTION (MAXRECURSION 0)
Go
— Short Script 10 – Obtendo o tamanho de índices Clustered e NonClustered —
SELECT COUNT(*) AS cached_pages_count,
COUNT(*)/128.0000 MB,
name AS BaseTableName,
IndexName,
IndexTypeDesc
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN (SELECT s_obj.name,
s_obj.index_id,
s_obj.allocation_unit_id,
s_obj.OBJECT_ID,
i.name IndexName,
i.type_desc IndexTypeDesc
FROM
(SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id,
allocation_unit_id,
OBJECT_ID
FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.TYPE = 1 OR au.TYPE = 3)
UNION ALL
SELECT OBJECT_NAME(OBJECT_ID) AS name,
index_id,
allocation_unit_id,
OBJECT_ID
FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.TYPE = 2
) AS s_obj LEFT JOIN sys.indexes i
ON i.index_id = s_obj.index_id
AND i.OBJECT_ID = s_obj.OBJECT_ID
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
and name not like ‘sys%’
and IndexName <> ‘null’
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY cached_pages_count DESC;
Muito bem, mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância e apresentam um valor e conhecimento do mais alto nível.

Chegamos ao final de mais um post, fique a vontade para compartilhar este conteúdo com seus contatos e redes sociais, contribua também enviando dicas, sugestões, dúvidas, críticas, enfim participe e ajude a melhorar cada vez mais esta sessão.

Mais uma vez obrigado por sua visita, nos encontramos em breve em mais um post da sessão Short Scripts.

Uma ótima semana, abraços.

Short Scripts – Março 2016


Olá comunidade, boa tarde amantes do SQL Server e Banco de dados!!!!

Após alguns meses fora do ar, mais uma sessão do meu blog esta retornando com muita força e disposição, estou me referindo a Short Scripts, sessão que durante os últimos anos tem conseguido alavancar a visitação do meu blog e principalmente ajudar a todos da comunidade na utilização de pequenos scripts dos mais variados possíveis assuntos relacionandos com o banco de dados mas principalmente ao Microsoft SQL Server.

Caso você não se lembre esta é uma sessão publicada a cada bimestre, onde durante o intervalo entre a publicação do post anterior e o novo post, vou adicionando novos arquivos “scripts” a minha biblioteca de scripts e posteriormente selecione aqueles que aparentemente podem ser considerados pequenos e de fácil uso e entendimento, mas que na verdade podem ajudar a resolver diversas situações ou até mesmo servir como recurso para implementação na busca da solução de grandes obstáculos que os profissionais de banco de dados e desenvolvimento possam estar se deparando.

Muitos dos scripts disponibilizados nesta sessão são resultado de pesquisas, posts publicados nos fóruns MSDN e também de respostas encontradas em outros websites dedicados ao SQL Server. Caso você não tenha acessado os últimos posts, não perca tempo, utilize os links publicados a seguir:

https://pedrogalvaojunior.wordpress.com/2015/12/14/short-script-dezembro-2015/

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/

Na relação de short scripts disponibilizados neste post, destaco os seguintes assuntos, conceitos ou funcionalidades:

  • Comando Like;
  • Comando Try…Catch;
  • Comando Print;
  • Concatenação de colunas e linhas;
  • Conversão de dados;
  • Dados Hexadecimais;
  • Database Owner;
  • Diretiva Set Ansi_Defaults;
  • Função Hashbytes;
  • Operador Bitwise;
  • Session Settings;
  • SET Implicit_Transactions;
  • String; e
  • Tipos de dados XML.

A seguir, apresento a relação de short scripts:

— Short Script 1 – Brincando com concatenação de linhas e colunas —

Select 1.8 E,1.8E,1.Eight,3+8E8Eight,

3+8 E8E,‘Six’+‘Seven’ ‘Eight’,‘Six’+‘Seven’+‘Eight’,

8.8Eight,‘Six”Seven’ ‘Eight’

Go

 

— Short Script 2 – Utilizando operador Bitwise para análise lógica de dados —

USE tempdb;

GO

 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = ‘bitwise’)

DROP TABLE bitwise;

GO

CREATE TABLE bitwise

(

a_int_value int NOT NULL,

b_int_value int NOT NULL

);

GO

INSERT bitwise VALUES (250, 255);

GO

 

USE tempdb;

GO

SELECT a_int_value | b_int_value

FROM bitwise;

GO

 

Select convert(binary, a_int_value | b_int_value) from bitwise

Go

 

— Short Script 3 – Trabalhando com XML – Apresentando os mesmos dados de forma diferente —

create table #customers (

id int,

customer varchar(50)

)

insert into #customers values (1,‘John’)

insert into #customers values (2,‘Lyss’)

insert into #customers values (3,‘Jack’)

insert into #customers values (4,‘David’)

insert into #customers values (5,‘Anne’)

insert into #customers values (6,‘Victoria’)

a)

SELECT 1 AS tag,

NULL AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersa

UNION ALL

SELECT 2 AS tag,

1 AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersb

ORDER BY [customers!2!customer] DESC,parent

FOR XML EXPLICIT, ROOT (‘customers’)

 

B)

SELECT id AS [customers/@id],

customer AS [customers/customers/@customer]

FROM #customers AS customesra

ORDER BY customer DESC

FOR XML PATH(),ROOT(‘customers’)

 

C)

SELECT customersa.id AS [customers.id],

customersb.customer AS [customers.customer]

FROM #customers AS customersa

INNER JOIN #customers AS customersb ON customersb.id=customersa.id

ORDER BY customersa.customer DESC

FOR XML AUTO, ROOT(‘customers’)

 

D)

DECLARE @x AS XML

SET @x=(

SELECT id, customer

FROM #customers AS customersa

FOR XML RAW(‘customer’),ROOT(‘customers’),TYPE

)

SELECT @x.query(

for $e in customers/customer

order by $e/@customer descending

return <customers id=”{$e/@id}”>

<customers customer=”{$e/@customer}”>

</customers>

</customers>’)

FOR XML RAW(‘customers’)

Go

 

— Short Script 4 – Descobrindo e Alterando o Database Owner —

— Obtendo a lista de Database Owners —

Select d.database_id,

d.name,

suser_name(d.owner_sid) as ‘Owner’,

d.user_access_desc,

d.compatibility_level

from sys.databases as d

Go

— Obtendo informações de um banco específico —

Exec sp_helpdb ‘Master’

Go

— Alterando o Database Owner —

Exec sp_changedbowner ‘NomedoNovoOwner’

Go

— Obtendo informações de um banco específico após alteração —

Exec sp_helpdb ‘Master’

Go

 

 

— Short Script 5 – Concatenação de string diretamente no select —

create table xxx (i int identity, a varchar(3))

go

declare @txt varchar(255)

set @txt = ‘Question’

select @txt = isnull (a, ‘?’) from xxx order by i

insert xxx values (‘of’)

insert xxx values (‘the’)

insert xxx values (‘day’)

select @txt = @txt + ‘ ‘ + a from xxx order by i

select @txt

Go

 

— Short Script 6 – Utilizando comando Like na junção de tabelas —

create table #Table1 (Col1 varchar(10))

create table #Table2 (Col1 varchar(10))

Go

insert into #table1 values (‘1’)

insert into #table2 values (‘1’)

insert into #table2 values (‘_1’)

insert into #table2 values (‘%1’)

insert into #table2 values (‘[_]1’)

insert into #table2 values (‘[%]1’)

Go

 

select * from #table1 t1 inner join #table2 t2

on t1.Col1 like t2.Col1

Go

 

 

— Short Script 7 – Session Settings – Utilizando ANSI_Defaults forçando SQL Server trabalhar com SET IMPLICIT_TRANSACTIONS —

Create Table QOD_Customers

(CompanyName Varchar(20),

Region nvarchar(15) Null)

Go

Insert Into QOD_Customers Values (‘A’,‘teste’)

Go 30

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create PROCEDURE [dbo].[QOD_Test_1]

AS

SET ANSI_DEFAULTS ON

— Before rollback Select Statement

SELECT COUNT(CompanyName) AS ‘Before rollback’ FROM [dbo].[QOD_Customers] WHERE [dbo].[QOD_Customers].[Region] IS NULL

UPDATE Dbo.QOD_Customers SET Region = ‘XXX’ WHERE dbo.QOD_Customers.region IS NULL

— The after update Select Statement

SELECT COUNT(CompanyName) AS ‘After update’ FROM [dbo].[QOD_Customers] WHERE [dbo].[QOD_Customers].[Region] IS NULL

ROLLBACK TRANSACTION

SET ANSI_DEFAULTS OFF

— The after rollback Select Statement

SELECT COUNT(CompanyName) AS ‘After Rollback’ FROM [dbo].[QOD_Customers] WHERE [dbo].[QOD_Customers].[Region] IS NULL

GO

 

Exec [QOD_Test_1]

Go

 

— Short Script 8 – Utilizando Try..Catch + Transaction + Print para apresentar mensagem —

declare @i int, @j int

set @i = 1

create table #temp (id int)

while (@i<=5)

begin

begin try

begin transaction

if (@i = 3)

set @j = @i/0

insert into #temp values (@i)

commit transaction

end try

begin catch

rollback transaction

print ‘this is an exception’;

end catch

set @i = @i + 1

end

 

select * from #temp

Go

 

— Short Script 9 – Trabalhando com a função Hashbytes para conversão de dados string para hexadecimal —

Select HASHBYTES(‘MD2’,‘January 1, 2016’)

Select HASHBYTES(‘MD4’,‘January 1, 2016’)

Select HASHBYTES(‘MD5’,‘January 1, 2016’)

Select HASHBYTES(‘SHA’,‘January 1, 2016’)

Select HASHBYTES(‘SHA1’,‘January 1, 2016’)

Select HASHBYTES(‘SHA2_256’,‘January 1, 2016’)

Select HASHBYTES(‘SHA2_512’,‘January 1, 2016’)

Go

 

 


 

Como de costume fique a vontade para compartilhar este conteúdo com seus contatos e redes sociais, contribua também enviando dicas, sugestões, dúvidas, críticas, enfim participe e ajude a melhorar cada vez mais esta sessão.

Mais uma vez obrigado por sua visita, nos encontramos em breve em mais uma post da sessão Short Scripts.

Valeu!!!

Short Script – Dezembro – 2015


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…..