Arquivo para categoria Banco de Dados

Ativando / Desativando Constraints e Triggers no Sql Server

Olá Caríssimos tudo bem ?

Hoje precisei fazer uma coisa medonha no SQL Server: Desativar todas as constraints e triggers de uma base para fazer uma importação de dados de uma base remota.

Devido a quantidade de tabelas e triggers preferi pesquisar algo em 30 minutos, do que passar o resto do dia fazendo essa tarefa.

Pois bem, no fim um coisa super simples de ser feita: peace-of-cake :

 

1-) Ativar e Desativar as Constraints

-- Desativando Foreign keys
DECLARE @table_name SYSNAME;
DECLARE @cmd NVARCHAR(MAX);
DECLARE table_cursor CURSOR FOR SELECT name FROM sys.tables;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @table_name;

WHILE @@FETCH_STATUS = 0 BEGIN
  SELECT @cmd = 'ALTER TABLE [' + @table_name + '] NOCHECK CONSTRAINT ALL';
  EXEC (@cmd);
  FETCH NEXT FROM table_cursor INTO @table_name;
END

CLOSE table_cursor;
DEALLOCATE table_cursor;

GO

--- Ativando as foreign keys
DECLARE @table_name SYSNAME;
DECLARE @cmd NVARCHAR(MAX);
DECLARE table_cursor CURSOR FOR SELECT name FROM sys.tables;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @table_name;

WHILE @@FETCH_STATUS = 0 BEGIN
  SELECT @cmd = 'ALTER TABLE [' + @table_name + '] CHECK CONSTRAINT ALL';
  EXEC (@cmd);
  FETCH NEXT FROM table_cursor INTO @table_name;
END

CLOSE table_cursor;
DEALLOCATE table_cursor;
 

2-) Ativar e Desativar as Triggers

---O mesmo código serve para habilitar e desabilitar,
DECLARE @enable BIT = 0;
DECLARE @trigger SYSNAME;
DECLARE @table SYSNAME;
DECLARE @cmd NVARCHAR(MAX);
DECLARE trigger_cursor CURSOR FOR SELECT trigger_object.name trigger_name,
table_object.name table_name
FROM sysobjects trigger_object
JOIN sysobjects table_object ON trigger_object.parent_obj = table_object.id
WHERE trigger_object.type = 'TR';

OPEN trigger_cursor;
FETCH NEXT FROM trigger_cursor INTO @trigger, @table;

WHILE @@FETCH_STATUS = 0 BEGIN
IF @enable = 1
SET @cmd = 'ENABLE ';
ELSE
SET @cmd = 'DISABLE ';

SET @cmd = @cmd + ' TRIGGER dbo.[' + @trigger + '] ON dbo.[' + @table + ']';
EXEC (@cmd);
FETCH NEXT FROM trigger_cursor INTO @trigger, @table;
END

CLOSE trigger_cursor;
DEALLOCATE trigger_cursor;

GO 

CUIDADO: Os códigos acima rodam para todas as tabelas!!!!

 

É isso.

Comentem, divulguem, é isso que mantém o blog no ar.
Grande Abraço

, , , , , , , , , ,

Deixe um comentário

UNPIVOT no SQL Server

Olá Caríssimos tudo bem ?

Em um post sobre banco de dados falei um pouco sobre o PIVOT no SQL. Um recurso bacana que gera uma “tabela dinâmica” transformando linhas em colunas no SQL Server.

E o contrário ? Transformar dado que estão em colunas em linhas ?

SIM!!! Isso é possível. Estava por esses dias fazendo uns desdobramentos para gerar combinações de números da Lotofácil (por isso talvez este seja meu último post, se eu ganhar vou tirar férias eternas!!!!) e precisei utilizar este recurso.

A tabela

Consultando no site da Caixa os resultados são baixados neste layout:

E o que preciso para conseguir gerar as estatísticas é esse layout:

Pra fazer isso é muito, muito simples, abra a query no SQL Server e siga o mesmo caminho abaixo:

SELECT Concurso, Dezenas,[Data Sorteio]
FROM
(SELECT Concurso,[Data Sorteio], Bola1, Bola2, Bola3, Bola4, Bola5, Bola6, Bola7, Bola8, Bola9, Bola10,
Bola11,Bola12,Bola13,Bola14,Bola15
FROM LOTFAC) PT
UNPIVOT
(Dezenas FOR Dez IN
(Bola1, Bola2, Bola3, Bola4, Bola5, Bola6, Bola7, Bola8, Bola9, Bola10,
Bola11,Bola12,Bola13,Bola14,Bola15)
)AS unpvt

Análise

1 – Observe que fiz uma subquery e dei o nome de PT, esta query deve ter todas as colunas que precisamos.

2 – Depois foi só utilizar o UNPIVOT para agregar os dados e colocar cada coluna como linha.

É SÓ ISSO!!!

Basta adaptar pra sua necessidade.

abraço a todos!!!

, , , , , , , , ,

3 Comentários

PIVOT no SQL Server parte II

Olá Caríssimos tudo bem ?

No post anterior falei sobre como fazer um PIVOT simples no SQL Server. (veja aqui)

Só uma recaptulação rápida: Utilizamos o PIVOT quando queremos mostrar linhas como colunas (transpor) em uma tabela.

No nosso exemplo tínhamos uma tabela de Vendas como o Layout :

E fizemos a query para mostrar a tabela assim:

Colocamos todas as cidades como Colunas.

Fizemos a query de uma forma simples, adicionando ‘hard code  o nome das cidades.

Select Marca,[São Paulo],[Rio de Janeiro],[Salvador],[Tarabai],[Campina Grande]
 from
 (
 Select Marca,Cidade,TotalVendido from vendas
 ) PT

PIVOT
 ( SUM(TotalVendido) for Cidade in ([São Paulo],[Rio de Janeiro],[Salvador],[Tarabai],[Campina Grande])) pvt

No fim do post levantei a questão se caso os itens que queremos mostrar como Colunas, neste caso cidades, fossem dinâmicas e como ficaria a consulta. Isso seria bem útil também para datas, já que é fora de questão ficarmos colocando datas que sempre mudam diretamente na query.

Quando digo dinâmicas, quero dizer que não sabemos o que virá como dado e que estes dados podem variar com o tempo, filtro ou qualquer outra situação.

No caso de dados dinâmicos temos que fazer alguns ajustes na query e torná-la dinâmica também:

Passo 1: Separar os dados que serão colunas

Vamos selecionar os valores que se tornarão colunas (no nosso caso eles estão na mesma tabela, mas não faz diferença se estiver em outra tabela relacionada) já no formato que queremos.

declare @values as nvarchar(max)
SELECT  @values = STUFF(( SELECT DISTINCT
'],[' + cidade
FROM    Vendas
ORDER BY '],[' + cidade
FOR XML PATH('')
), 1, 2, '') + ']'

Esse código irá gerar algo do tipo:


[Campina Grande],[Rio de Janeiro],[Salvador],[São Paulo],[Tarabai]

Passo 2: Preparar o esqueleto da query

Agora vamos criar o esqueleto da consulta final:


declare @query as nvarchar(max)
set @query = 'Select Marca, |@
from
(
Select Marca,Cidade,TotalVendido from vendas
) PT

PIVOT
( SUM(TotalVendido) for Cidade in ( |@ )) pvt'

OBS: Na query acima coloquei o marcador ‘|@’, que será substituído pelos valores das cidades.

Passo 3: Substituir os marcadores pelos valores obtidos no passo 1, executar a query e correr pro abraço!!!!!!!

set @query = REPLACE(@query,'|@',@values)
exec(@query)

Nosso código completo:

--- Separando os valores: Passo 1
declare @values as nvarchar(max)
SELECT  @values = STUFF(( SELECT DISTINCT
'],[' + cidade
FROM    Vendas
ORDER BY '],[' + cidade
FOR XML PATH('')
), 1, 2, '') + ']'

--- Esqueleto da query com os marcadores: Passo 2
declare @query as nvarchar(max)
set @query = 'Select Marca, |@
from
(
Select Marca,Cidade,TotalVendido from vendas
) PT

PIVOT
( SUM(TotalVendido) for Cidade in ( |@ )) pvt'
set @query = REPLACE(@query,'|@',@values)
--- Executando a query
exec(@query)

É isso, está feito.
Não é o melhor dos mundos esta solução mas não há outra forma.

Abraço a todos!!

, , , , , , , ,

14 Comentários

PIVOT no SQL Server parte I

Olá Caríssimos tudo bem ?

Neste post estarei mostrando como é simples realizar uma consulta para gerar um  resultado que seria como uma tabela de referência cruzada (pivot table, transposição de tabela, tabela dinâmica, etc) no SQL Server.

Vamos dividir o post em duas partes, nesta vamos fazer algo simples e no próximo post faremos algo mais avançado.

Uma referência cruzada ou Pivot Table é necessária quando queremos efetuar uma transposição nos dados, realizando algum cálculo (ou não).

Imagine o seguinte: Temos uma tabela com todas as vendas por marca e por cidade e desejamos exibir os resultados colocando as marcas como linhas e as cidades como colunas, assim cada marca terá somente uma linha na consulta final. Assim deixamos o resultado mais resumido e de fácil visualização.

A tabela

Nossa tabela VENDAS tem o layout assim:

Como citei anteriormente queremos visualizar as cidades como colunas e o layout final ficará assim:

Pra fazer isso é muito, muito simples, abra a query no SQL Server e siga o mesmo caminho abaixo:

Select Marca,[São Paulo],[Rio de Janeiro],[Salvador],[Tarabai],[Campina Grande]
 from
 (
 Select Marca,Cidade,TotalVendido from vendas
 ) PT

PIVOT
 ( SUM(TotalVendido) for Cidade in ([São Paulo],[Rio de Janeiro],[Salvador],[Tarabai],[Campina Grande])) pvt

Análise

1 – Observe que fiz uma subquery e dei o nome de PT, podíamos ter adicionado até uma função agregada. O importante é esta query ter todas as colunas que precisamos.

2 – Depois foi só utilizar o PIVOT para agregar os dados e colocar as linhas como colunas.

É SÓ ISSO!!!

Basta adaptar pra sua necessidade.

Você deve estar questionando  OK. E se minhas colunas forem dinâmicas, se variarem dependendo de um filtro ?

A resposta será dada no próximo POST (PIVOT no Sql Server parte II) .

abraço a todos!!!

, , , , , ,

4 Comentários

Exportando colunas Image e Binary no SQL Server para JPEG

Olá Caríssimos, tudo bem ?

Hoje vamos resolver uma questão que há dias incomodava: Como faço pra exportar para JPEG uma imagem gravada em uma coluna do tipo image / binary no SQL Server ?

Depois de procurar bastante achei aqui: http://michaelsync.net/2010/07/20/easy-sql-script-for-handling-sql-image-data

Primeiro é preciso habilitar OLE Automation Procedures no banco. Você precisará logar com um usuário com permissões administrativas.


sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

OK! Agora já podemos exportar a imagem executando o código a seguir:


DECLARE @pctStr INT
DECLARE @image VARBINARY(MAX)
--- aqui você substitui pela tabela e faz a query. Você também pode criar uma proc que exporte tudo
SET @image = (SELECT foto FROM TbFotos WHERE codfoto= 121)
DECLARE @filePath VARCHAR(8000)
SET @filePath = 'C:\\foto.jpg'
EXEC sp_OACreate 'ADODB.Stream', @pctStr OUTPUT
EXEC sp_OASetProperty @pctStr, 'Type', 1
EXEC sp_OAMethod @pctStr, 'Open'
EXEC sp_OAMethod @pctStr,  'Write', NULL, @image
EXEC sp_OAMethod @pctStr, 'SaveToFile', NULL,@filePath, 2
EXEC sp_OAMethod @pctStr, 'Close'
EXEC sp_OADestroy @pctStr

Lembrando que: o Path que você passa é do servidor de instalação do SQL e não da sua máquina…

É só isso.

Enjoy it !!!

abraço a todos!

, , , , , ,

6 Comentários

Concatenando dados em um único campo utilizando FOR XML PATH no SQL Server

Olá a todos!!!

Hoje gostaria de compartilhar um recurso do SQL Server que geralmente não é utilizado para o fim que apresento aqui, mas que foi muito interessante e útil pra mim: o recurso FOR XML PATH.

Este recurso é ótimo e bem útil para gerar consultas com resultados em XML (ajuda aqui), a perfomance da consulta também é muito boa, mas fui mais além e utilizei esse recurso para resolver um problema que estava me incomodando.

Imagine que você tem uma tabela A com relacionamento um para muitos com uma tabela B. Precisamos montar uma consulta onde em apenas uma linha apresentemos o resultado de todos os dados da tabela A e B.

Mas como fazer se o relacionamento é um para muitos ?

O Problema

Criar uma consulta que retorne os dados dos clientes e os motivos para cancelar a assinatura do serviço (cada cliente deve ser apresentado em uma linha só). Na tabela tbClientes, temos os dados dos clientes, e na tabela tbMotivosCancelamento, temos todos os motivos de cancelamento de cada cliente. (Para simplificar, neste exemplo não vou normalizar a tabela de motivos de cancelamento, vamos considerar que o texto com o motivo foi gravado diretamente na tabela tbMotivosCancelamento).

Modelo das Tabelas

Até descobrir este recurso estava utilizando uma function que para cada cliente retornava os motivos do cancelamento, mas na function era executada uma outra consulta, assim, para cada cliente uma consulta era executada, fazendo o custo da consulta ser muito alto.

O layout final da consulta deve ser algo do tipo:

Note que no resultado final, cada motivo é separado pelo caractere ‘-‘ e apresentado em um único campo, porém na tabela tbMotivosCancelamento cada motivo é gravado separadamente.

A solução

Utilizando o FOR XML PATH, neste caso teríamos o seguinte:

SELECT  Nome as ‘Nome do Cliente’, Cidade, UF, STUFF
                          ((SELECT     ‘ – [‘ + mc.MotivoCancelamento + ‘] ‘ AS [data()]
                              FROM         dbo.[tbMotivosCancelamento] mc
                              WHERE     c.Codigo = mc.CodCliente FOR XML PATH(”)),1,3,”) AS [Motivos do Cancelamento]
FROM         tbClientes c

That is it!!!!  Está feito…

O ganho de perfomance no meu caso foi de mais de 500% em relação à utilização da function.

Notem que utilizei o comando STUFF, ele serve apenas para eliminar o ‘-‘ do início do texto.

Até a próxima pessoal …

, , , , , , ,

Deixe um comentário