SQLSat 677 – Salvador

E lá vamos nós: Salvador!

Dia 18 de novembro de 2017 teremos mais um evento sobre SQLServer, o #SQLSat 677.

Tive o prazer de ter uma palestra aceita: Carreiras – DBA, DA, Cientista de Dados

Para mais informações e inscrições:  http://www.sqlsaturday.com/677/Sessions/Schedule.aspx

Nos vemos lá!!

21249ef1-f07d-4486-bb18-98fc1b8c4344

Anúncios

SQLSATURDAY 676 #SaoPaulo

E hoje recebo mais uma notícia fantastica:

Minha palestra sobre carreiras foi aprovada para o SQLSaturday 676 de São Paulo, que será dia 30 de setembro de 2017.

Irei falar sobre as diferenças entre as carreiras de DA, DBA, Cientista de dados e Engenheiro de dados e como o mercado atual esta enxergando (ou não) as diferenças.

Para quem não conhece, o SQLSat como é popularmente chamado, é um sábado todo de muitas palestras segmentados por trilhas.

Este ano teremos trilhas de:

Big Data e Data Science
Business Intelligence
Cloud Computing
Carreira e Certificação
Database Administration & Deployment

Se inscreva já! Não fique fora dessa! E é de graça!!!

Mais informações em:

http://www.sqlsaturday.com/676/EventHome.aspx

 

 

TDC – Evento de Julho

Opa!!!!
Voltando!!!! e com muitas novidades!!!
Apesar da ausencia no blog, muita coisa aconteceu e aos poucos vou liberando.
Teremos muitos artigos técnicos, muitos posts de case do dia a dia e vamos que vamos.

Hoje vou falar de um evento que tive o enorme prazer em palestrar.

The Developers Conference

Para quem não conhece o evento, segue o link para conhecer:
TDC Facebook

Foi minha primeira palestra fora do círculo de empresas, então foi gratificante, para dizer o minimo.

Eu falei um pouco sobre uma migração em que participei do Oracle para o Azure.
Esta migração considerado fi considerado pela Microsoft como a maior migração para o Azure da América Latina, e claro que este assunto eu vou falar ainda esta semana e em outro post.

Apesar o tempo que cadastrei minha palestra, consegui apresentar os desafios desta migração, erros e acertos e claro, conheci pessoas em que tive contato somente “on line”.

As fotos que consegui tirar estão logo abaixo.

Em breve, mais novidade!

Até mais pessoal!

Bem Vindo TDC

Cracha de identificação

Placa de localização das palestras


_Frente_003_Frente_001

_Frente_002

_Frente

 

 

 

 

 

 

 

 

 

 

TSQL – Clima Natalino

Tags

, , ,

Revisando uma biblioteca de scripts, me deparei com uma função que utilizo bem pouco: geometry::STMPointFromText
Na verdade creio que a utilizei somente umas duas vezes.

E executando o script obtenho a imagem abaixo. Não me recordo a origem, mas achei na época bem interessante. Se alguém souber o fonte, por favor me informe para eu colocar os devidos créditos.

papai_noel

Auditoria em processos

Tags

, , , ,

Recentemente alguns usuários estavam se queixando de lentidão em um determinado sistema.
Como sempre, fui dar uma olhada nos processos, porém precisava de algo que me desse mais detalhes do que a sp_who2. Poderia pegar as sessões pela sys.dm_exec_requests e pela sys.dm_exec_sessions porém me lembrei da sys.dm_exec_sql_text que retorna o conteúdo da execução.
Com isso, montei a sintaxe abaixo que me retorna o que esta sendo executado na instancia, bem como a linha de comando, tempo de execucao, programa…


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
	AND session_Id NOT IN (@@SPID)
ORDER BY 1, 2

Bloquear uso do “SSMS” para logar em determinada instancia

Tags

, , ,

Em alguns ambientes se torna necessário restringir o acesso a base de dados pelo SSMS (Microsoft SQL Server Management Studio), seja por regra de negócio ou restrição do próprio ambiente. Mas como bloquear o acesso pelo SSMS sem bloquear o usuário da aplicação?

Simples: Trigger de Logon. Segue um exemplo que utilizei:

GRANT VIEW SERVER STATE TO [LOGIN]; -- COLOCA O LOGIN DO USUARIO A SER BLOQUEADO
GO

CREATE TRIGGER BLOQUEIO_SSMS
ON ALL SERVER WITH EXECUTE AS 'sa' 
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT 1 FROM sys.dm_exec_sessions
				WHERE is_user_process = 1 
					AND original_login_name = 'LOGIN' -- COLOCA O LOGIN DO USUARIO A SER BLOQUEADO
					and program_name like '%Management%') -- LISTA OS PROGRAMAS A SEREM BLOQUEADOS
    ROLLBACK;
END;

Busca determinada expressão em todos os campos de uma base de dados

Tags

, , , ,

Quase fazendo um ano de distancia do blog por motivos profissionais, vou postar uma dica rápida, porém útil em diversas situações.
Uma das situações que identifiquei, foi um banco mal modelado onde eu precisava fazer uma busca por um determinado CPF em todos os campos das tabelas de uma database. Claro que não preciso nem citar que este tipo de consulta deve ser muito, mas muito bem pensada antes de se fazer em produção, ou melhor ainda, não faça em produção! Onera muito a performance, pois são vários selects com like em todos os campos!!

Vamos ao script:

Script01:

set nocount on
go

DECLARE @SQL VARCHAR(max)
DECLARE @filtro VARCHAR(max)
DECLARE @filtro_www VARCHAR(max)
 
-- inicia a declaração do sql
SET @SQL = ''
SET @filtro = '%VALOR_A_SER_PROCURADO%'

 if object_id('tempdb..#result') is not null
 drop table #result
 
SELECT
   tabelas.name   AS Tabela 
  ,colunas.name   AS Coluna
  ,tipos.name     AS Tipo
  ,colunas.LENGTH AS Tamanho
INTO
  #result
FROM sysobjects tabelas  INNER JOIN syscolumns colunas
		ON colunas.id = tabelas.id
	INNER JOIN systypes tipos
		ON tipos.xtype = colunas.xtype
WHERE tabelas.xtype = 'U'
--    AND tipos.name IN('text', 'ntext', 'varchar', 'nvarchar')
 
-- cursor para varrer as tabelas
DECLARE cTabelas cursor LOCAL fast_forward FOR
SELECT DISTINCT Tabela FROM #result
 
DECLARE @nomeTabela VARCHAR(max)
 
OPEN cTabelas
 
fetch NEXT FROM cTabelas INTO @nomeTabela
 
while @@fetch_status = 0
BEGIN
 
  -- cursor para varrer as colunas da tabela corrente
  DECLARE cColunas cursor LOCAL fast_forward FOR
  SELECT Coluna, Tipo, Tamanho FROM #result WHERE Tabela = @nomeTabela
 
  DECLARE @nomeColuna VARCHAR(max)
  DECLARE @tipoColuna VARCHAR(max)
  DECLARE @tamanhoColuna VARCHAR(max)
 
  OPEN cColunas
 
  -- monta as colunas da cláusula select 
  fetch NEXT FROM cColunas INTO @nomeColuna, @tipoColuna, @tamanhoColuna
 
  while @@fetch_status = 0
  BEGIN
    -- cria a declaração da variável
    SET @SQL = 'declare @hasresults bit' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
    -- cria o select
    SET @SQL = @SQL + 'select' + CHAR(13) + CHAR(10)
    SET @SQL = @SQL + CHAR(9) + '''' + @nomeTabela + ''' AS NomeTabela'
    SET @SQL = @SQL + CHAR(9) + ',' + @nomeColuna + CHAR(13) + CHAR(10)
    -- adiciona uma coluna com o tipo e o tamanho do campo
    SET @SQL = @SQL  + CHAR(9) + ',' + '''' + @tipoColuna + ''' AS ''' + @nomeColuna + '_Tipo''' + CHAR(13) + CHAR(10)
    SET @SQL = @SQL  + CHAR(9) + ',' + 'DATALENGTH(' + @nomeColuna + ') AS ''' + @nomeColuna + '_Tamanho_Ocupado''' + CHAR(13) + CHAR(10)    
    SET @SQL = @SQL  + CHAR(9) + ',' + '''' + @tamanhoColuna + ''' AS ''' + @nomeColuna + '_Tamanho_Maximo''' + CHAR(13) + CHAR(10)
 
    -- define a tabela temporária (#result)
    SET @SQL = @SQL + 'into' + CHAR(13) + CHAR(10) + CHAR(9) + '#result_' + @nomeTabela + CHAR(13) + CHAR(10)
    -- adiciona a cláusula from
    SET @SQL = @SQL +  'from' + CHAR(13) + CHAR(10) + CHAR(9) + @nomeTabela + CHAR(13) + CHAR(10)
    -- inicia a montagem do where
    SET @SQL = @SQL + 'where' + CHAR(13) + CHAR(10)
    SET @SQL = @SQL + CHAR(9) + @nomeColuna + ' like ''' + @filtro + '''' + CHAR(13) + CHAR(10)
 
    SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'select @hasresults = count(*) from #result_' + @nomeTabela + CHAR(13) + CHAR(10)
    SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'if @hasresults > 0'
    SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'begin'
    SET @SQL = @SQL + CHAR(13) + CHAR(10) + CHAR(9) + 'select * from #result_' + @nomeTabela
    SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'end' + CHAR(13) + CHAR(10)
    SET @SQL = @SQL + CHAR(13) + CHAR(10) + 'drop table #result_' + @nomeTabela
    SET @SQL = @SQL + CHAR(13) + CHAR(10)
 
    fetch NEXT FROM cColunas INTO @nomeColuna, @tipoColuna, @tamanhoColuna
	-- descomente a linha abaixo para ver o SQL produzido no janela de Messages
    -- print @sql
    EXEC(@SQL)
    SET @SQL = ''
  END
 
  close cColunas
  deallocate cColunas
 
  fetch NEXT FROM cTabelas INTO @nomeTabela
END
 
close cTabelas
deallocate cTabelas

Script02:

set nocount on 
go


declare @idtabela int, @tabela varchar(max), @coluna varchar(max), @valorProcurado varchar(max)

--Coloque aqui a palavra ou expressão que deseja procurar
set @valorProcurado = 'VALOR_A_SER_PROCURADO' 

if OBJECT_ID('tempdb..#tmpFindString') is not null
drop table #tmpFindString

create table #tmpFindString (table_name varchar(max), string varchar(max))

DECLARE db_cursor CURSOR FOR  
		select id from sys.sysobjects where xtype = 'U'
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @idtabela

WHILE @@FETCH_STATUS = 0   
BEGIN   

		DECLARE db_cursorColunas CURSOR FOR  
			select a.name as tabela, b.name as coluna from sys.sysobjects a
				inner join
					sys.syscolumns b
				on a.id = b.id 
			 where a.xtype = 'U'
					and a.id = @idtabela
		OPEN db_cursorColunas   
		FETCH NEXT FROM db_cursorColunas INTO @tabela, @coluna

		WHILE @@FETCH_STATUS = 0   
		BEGIN   
				exec('
				insert #tmpFindString
				select '''  + @tabela + ''', string = '''+@valorProcurado+'''
						from ' + @tabela + ' where ['+@coluna+'] like ''%'+@valorProcurado+'%''')
			   
			   FETCH NEXT FROM db_cursorColunas INTO @tabela, @coluna 
		END   

		CLOSE db_cursorColunas   
		DEALLOCATE db_cursorColunas 
	   
	   FETCH NEXT FROM db_cursor INTO @idtabela 
END   

CLOSE db_cursor   
DEALLOCATE db_cursor 

	   
select distinct * from #tmpFindString

Cálculo simples de data

Vejo sempre em fóruns muitos usuários com dúvidas sobre cálculos com datas, então resolvi postar alguns exemplos deste cálculo utilizando as funções DATEDIFF, DATEPART e DATEWEEK.
As funções funcionam desde a versão SQL2005. Na versão 2012 temos outras que devem ser material de um próximo tópico.
Em todos os exemplos, estou supondo que o campo de data esta como DATETIME ou SMALLDATETIME e a language como english.

Primeiro vamos conhecer os tipos de datas convertidas

SELECT CONVERT(VARCHAR(12),GETDATE(),101) AS '101', --mm/dd/aaaa
CONVERT(VARCHAR(12),GETDATE(),102) AS '102', --aa.mm.dd
CONVERT(VARCHAR(12),GETDATE(),103) AS '103', --dd/mm/aaaa
CONVERT(VARCHAR(12),GETDATE(),104) AS '104', --dd.mm.aa
CONVERT(VARCHAR(12),GETDATE(),105) AS '105', --dd-mm-aa
CONVERT(VARCHAR(12),GETDATE(),106) AS '106', --dd mês aa
CONVERT(VARCHAR(12),GETDATE(),107) AS '107', --Mês dd, aa
CONVERT(VARCHAR(12),GETDATE(),108) AS '108', --hh:mi:ss
CONVERT(VARCHAR(12),GETDATE(),109) AS '109', --mês dd aaaa hh:mi:ss:mmmAM (ou PM)
CONVERT(VARCHAR(12),GETDATE(),110) AS '110', --mm-dd-aa
CONVERT(VARCHAR(12),GETDATE(),111) AS '111', --aa/mm/dd
CONVERT(VARCHAR(12),GETDATE(),112) AS '112', --aammdd
CONVERT(VARCHAR(12),GETDATE(),113) AS '113', --dd mês aaaa hh:mi:ss:mmm (24h)
CONVERT(VARCHAR(12),GETDATE(),114) AS '114', --hh:mi:ss:mmm(24h)
CONVERT(VARCHAR(12),GETDATE(),120) AS '120', --aaaa-mm-dd hh:mi:ss(24h)
CONVERT(VARCHAR(12),GETDATE(),121) AS '121' --aaaa-mm-dd hh:mi:ss.mmm(24h)

Agora um cálculo básico: Cálculo de idade
Para cálculo de idade, utilizo a função DATEDIFF.

/* TABELA COM DADOS FICTIOS */
declare @tabela table (nome varchar(10),  data_nascimento datetime)

insert into @tabela values ('nome_01', '1980-01-01')
insert into @tabela values ('nome_02', '1990-02-11')
insert into @tabela values ('nome_03', '1976-03-21')
insert into @tabela values ('nome_04', '1965-04-15')
insert into @tabela values ('nome_05', '1986-05-16')
insert into @tabela values ('nome_06', '1800-06-17')




SELECT 
    *  -- TODOS OS REGISTROS
    , datediff(yy, data_nascimento, getdate()) as idade -- parametro YY ou YEAR para mostrar o resultado em ANOS.
FROM @tabela

–Results
nome data_nascimento idade


nome_01 1980-01-01 00:00:00.000 34
nome_02 1990-02-11 00:00:00.000 24
nome_03 1976-03-21 00:00:00.000 38
nome_04 1965-04-15 00:00:00.000 49
nome_05 1986-05-16 00:00:00.000 28
nome_06 1800-06-17 00:00:00.000 214

Para ficar mais interessante: Que dia da semana o meu usuário nasceu?
Função: DATEPART com o parametro WEEKDAY

-- DIA 07/06/2014 vai cair que dia da semana?
SELECT DATEPART(WEEKDAY,'2014-06-07') 


-- Results
-----------
   7

Mas o que siginifica este 7?
O DATEPART tem como retorno um campo INT, com isso o número dos dias da semana fica assim:
DOMINGO = 1
SEGUNDA =2
TERCA = 3
QUARTA = 4
QUINTA = 5
SEXTA = 6
SABADO = 7
Portanto dia 07/06/2014 será um Sábado.

Porém fica complicado usar um CASE para cada dia da semana, então agora vamos usar o DATENAME que tem como retorno o dia da semana por extenso:

SELECT DATENAME(WEEKDAY,'2014-06-07') 

-- Results
------------------------------
Saturday

Simples?! Agora vamos testar as funções com a seguinte situação:
Fechamento de horas de cartão de ponto, onde eu tenha que contar somente os dias da semana (segunda a sexta)
Neste caso, não temos tabelas com feriados, portanto vale de segunda a sexta.

<br />/*****************************************************
CONTA AS HORAS ÚTEIS ENTRE A DATA INICIAL (@INICIO)
E A DATA FINAL (@FINAL)
*****************************************************/


CREATE function fn_HorasUteis (
    @dt_inicio smalldatetime,
    @dt_final smalldatetime
)

returns varchar(max)
as

begin
DECLARE @fimdesemana INT
DECLARE @i INT

/**** CALCULO DE FINAL DE SEMANA ****/
SELECT @fimdesemana = 0, @i = 0 
WHILE (DATEDIFF(day, @dt_inicio, @dt_final) &gt; @i) 
BEGIN
    SELECT @i = @i + 1
    IF (DATEPART(WEEKDAY, DATEADD(day, @i, @dt_inicio)) in (1,7)) 
        BEGIN
        SELECT @fimdesemana = @fimdesemana + 1
        END
END


declare @dia numeric(30),@hora numeric(30), @minuto numeric(30)

    SELECT @dia =    DATEDIFF(d, @dt_inicio, @dt_final) 
    SELECT @hora =    DATEDIFF(hour, @dt_inicio, @dt_final) 
    select @minuto =  DATEDIFF(MINUTE, @dt_inicio, @dt_final) 

    if @dia &gt; 0 
    begin
        SET @fimdesemana = (@fimdesemana * 8)
        SET @hora = @hora - (@dia * 24) - @fimdesemana
        SET @dia = @dia * 10


    end

return 
convert(varchar(max),(@hora +@dia)) +':'+convert(varchar(max),(@minuto %60))

end

Exemplo de uso:
SELECT dbo.fn_HorasUteis(‘2013-01-29 08:00:00′,’2013-01-29 18:00’)

— Results

10:00

Podemos então alterar nossa primeira tabela para trazer o dia da semana em que o usuário nasceu:

/* TABELA COM DADOS FICTIOS */
declare @tabela table (nome varchar(10),  data_nascimento datetime)

insert into @tabela values ('nome_01', '1980-01-01')
insert into @tabela values ('nome_02', '1990-02-11')
insert into @tabela values ('nome_03', '1976-03-21')
insert into @tabela values ('nome_04', '1965-04-15')
insert into @tabela values ('nome_05', '1986-05-16')
insert into @tabela values ('nome_06', '1800-06-17')




SELECT 
    *  -- TODOS OS REGISTROS
    , datediff(yy, data_nascimento, getdate()) as idade -- parametro YY ou YEAR para mostrar o resultado em ANOS.
    , DATENAME(weekday,data_nascimento) as [dia_semana]
FROM @tabela

–Results
nome data_nascimento idade dia_semana


nome_01 1980-01-01 00:00:00.000 34 Tuesday
nome_02 1990-02-11 00:00:00.000 24 Sunday
nome_03 1976-03-21 00:00:00.000 38 Sunday
nome_04 1965-04-15 00:00:00.000 49 Thursday
nome_05 1986-05-16 00:00:00.000 28 Friday
nome_06 1800-06-17 00:00:00.000 214 Tuesday

Em uma segunda parte, vou utilizar o SQL2012.