Tag: sql server

SQL Server: comparando duas bases de dados com uma query

sql server

Muitos colegas já tiveram a necessidade de comparar duas bases de dados no SQL Server para analisar rapidamente a diferença entre elas.

Como é algo recorrente, estou publicando aqui uma consulta (query) que compara a estrutura de duas bases e destaca tabelas e campos que existem em uma e não na outra, bidirecionalmente.

Bases de exemplo

Imagine que você tem um BANCO_A:

use BANCO_A;
go

create table Person (
    id int primary key identity,
    name varchar(100),
    height numeric(4,1)
);

create table Car (
    id int primary key identity,
    brand varchar(100),
    model varchar(100),
    year int 
);

create table Animal (
    id int primary key identity,
    name varchar(100),
    kind varchar(100)
);

E também um BANCO_B:

use BANCO_B;
go

create table Person (
    id int primary key identity,
    name varchar(100)
);

create table Car (
    id int primary key identity,
    brand varchar(100),
    model varchar(100),
    year float,
    kilometers int
);

create table Pet (
    id int primary key identity,
    name varchar(100)
);

Verificando tabelas adicionadas e excluídas

Para identificar somente as tabelas que foram adicionadas ou excluídas de uma base para outra, use a seguinte consulta:

SELECT T1.TABLE_NAME 'DB1 TABLE', T2.TABLE_NAME 'DB2 TABLE'
FROM BANCO_A.INFORMATION_SCHEMA.TABLES T1 
FULL JOIN BANCO_B.INFORMATION_SCHEMA.TABLES T2 
    ON T1.TABLE_NAME = T2.TABLE_NAME
ORDER BY ISNULL(T1.TABLE_NAME, T2.TABLE_NAME)

Verificando colunas adicionadas e excluídas

Para verificar as diferenças tanto das tabelas como das colunas que elas contém, use a seguinte consulta:

SELECT DB1.TABLE_NAME 'DB1 TABLE', DB1.COLUMN_NAME 'DB1 COLUMN', DB1.DATA_TYPE 'DB1 TYPE',
    DB2.TABLE_NAME 'DB2 TABLE', DB2.COLUMN_NAME 'DB1 COLUMN', DB2.DATA_TYPE 'DB2 TYPE'
FROM (
    SELECT T1.TABLE_NAME, C1.COLUMN_NAME, C1.DATA_TYPE
    FROM BANCO_A.INFORMATION_SCHEMA.TABLES T1 
    JOIN BANCO_A.INFORMATION_SCHEMA.COLUMNS C1 
        ON C1.TABLE_NAME = T1.TABLE_NAME
    ) DB1
FULL JOIN (
    SELECT T2.TABLE_NAME, C2.COLUMN_NAME, C2.DATA_TYPE
    FROM BANCO_B.INFORMATION_SCHEMA.TABLES T2 
    JOIN BANCO_B.INFORMATION_SCHEMA.COLUMNS C2 
        ON C2.TABLE_NAME = T2.TABLE_NAME
    ) DB2
    ON DB1.TABLE_NAME = DB2.TABLE_NAME
    AND DB1.COLUMN_NAME = DB2.COLUMN_NAME
ORDER BY ISNULL(DB1.TABLE_NAME, DB2.TABLE_NAME), ISNULL(DB1.COLUMN_NAME, DB2.COLUMN_NAME)

Executando a consulta dinamicamente

Nos dois exemplos acima, basta trocar BANCO_A e BANCO_B por duas bases que você precisa comparar.

Entretanto, pode ser que você queira criar uma procedure ou rotina que compare duas bases quaisquer.

Para isso você pode executar uma consulta dinâmica usando o comando SP_SQLEXEC. Veja o seguinte exemplo:

DECLARE 
    @BANCO1 NVARCHAR(100) = 'BANCO_A',
    @BANCO2 NVARCHAR(100) = 'BANCO_B',
    @SQL NVARCHAR(2000)

SET @SQL = N'SELECT T1.TABLE_NAME ''DB1 TABLE'', T2.TABLE_NAME ''DB2 TABLE''
    FROM ' + @BANCO1 + '.INFORMATION_SCHEMA.TABLES T1 
    FULL JOIN ' + @BANCO2 + '.INFORMATION_SCHEMA.TABLES T2 
        ON T1.TABLE_NAME = T2.TABLE_NAME
    ORDER BY ISNULL(T1.TABLE_NAME, T2.TABLE_NAME)';

EXEC sp_sqlexec @SQL

Agora basta alterar o valor das variáveis ou receber os nomes das duas bases através de parâmetros.

Duas observações importantes:

  1. O parâmetro da rotina SP_SQLEXEC deve ser do tipo NVARCHAR.
  2. Não tente fazer a concatenação de variáveis e literais diretamente no argumento dessa rotina. Faça isso sempre antes e então passe uma variável como argumento. Não fiz o teste em todas as versões do SQL Server, mas nas que usei deve ser desta forma.

Comportamento recursivo inesperado no SQL Server

Como todo desenvolvedor, provavelmente você já perdeu horas com um problema que beira o bizarro até perceber que era algo muito simples.

Uma colega estava testando um sistema onde uma procedure era acionada e, após algum tempo, ocorria um erro “Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)”.

O erro refere-se ao limite de chamadas empilhadas ou recursivas, por exemplo, uma função que chama outra, que chama outra e assim por diante. Entretanto, a procedure não tinha nenhuma chamada desse tipo.

Analisei por alguns segundos os script de criação da procedure, algo como:

CREATE PROCEDURE MINHA_PROC (...) AS
BEGIN
... conteúdo ...
END
EXEC PROCEDURE MINHA_PROC [valores de teste]

Então lembrei de uma “pegadinha” de sintaxe. Para o SQL Server, o conteúdo de uma procedure é tudo o que está entre o CREATE e o “GO”. É o equivalente à barra invertida no Oracle.

Repare que no código de exemplo não existe um GO, pois o desenvolvedor assumiu que o bloco BEGIN/END demarcaria a procedure, mas não é o caso. O comando “EXEC” usado para testar a procedure acabou incluído como parte dela e, quando executada, criou chamadas recursivas até estourar o limite.

Solução? Tão simples quando um “GO”:

CREATE PROCEDURE MINHA_PROC (...) AS
BEGIN
... conteúdo ...
END
GO
EXEC PROCEDURE MINHA_PROC [valores de teste]

Uma forma simples de contar registros e limpar tabelas de uma base de dados

Uma colega estava com alguns backups de uma base de dados SQL Server para realizar testes e queria diminuir o tamanho das bases limpando tabelas de log e operacionais, mantendo apenas as configurações do sistema. O problema é que as bases eram tão grandes que ultrapassavam o limite de 10 GB estabelecido na versão SQL Express 2008.

Ele não precisava e nem queria entender a estrutura de tabelas, então sugeri uma maneira rápida e simples de identificar tabelas com muitos registros e uma forma de excluí-las rapidamente.

A query abaixo retorna vários selects que exibem o total de registros de cada tabela de um banco de dados:

use NOME_BASE
go
select 'select ''' + name + ''', count(*) from ' + name from sys.tables

Basta executar esse código, substituindo o nome da base a ser usada, copiar o resultado gerado e executá-lo.

Esta query gera deletes para cada tabela, no caso de ser necessário limpar a base toda:

select 'delete from ' + name from sys.tables

A versão usando truncate:

select 'truncate table ' + name from sys.tables

Inserção em massa no SQL Server

Como inserir vários registros em uma tabela com valores sequenciais? Por exemplo, como podemos obter uma série de datas?

A resposta mais simples seria utilizar um cursor ou laço (loop), mas a execução de ambos é demasiadamente lenta para muitos registros. O desempenho máximo somente será obtido se a inserção for feita de uma só vez em um comando INSERT. Para isso, podemos usar uma tabela auxiliar com valores sequenciais de forma que possamos usar esses valores para derivar aqueles que necessitamos. Mas como obter tal tabela?

Uma das formas de unir velocidade e praticidade é criar uma tabela com um campo IDENTITY. Entretanto, não gosto da ideia de criar uma tabela física para auxiliar em operações completamente ortogonais às funcionalidades do sistema.

Opção fácil para poucos registros

A tabela MASTER..SPT_VALUES possui números sequenciais de 0 (zero) a 2047 e é nativa do SQL Server. O exemplo abaixo insere 2000 linhas com datas sequenciais de uma só vez:

INSERT INTO DATAS (DATA, DIADASEMANA)
SELECT DATEADD(d, NUMBER, @DATAINICIAL), DATEPART(DW,DATEADD(d, NUMBER, @DATAINICIAL))
FROM MASTER..SPT_VALUES
WHERE TYPE='P' AND NUMBER BETWEEN 0 AND 1999

Portanto, com essa tabela é possível fazer uma inserção em massa utilizando a coluna NUMBER e alguma função que calcule o valor do campo a partir do valor sequencial. No exemplo, a função DATEADD gera datas a partir da @DATAINICIAL, até 2000 dias à frente, e também armazena o dia da semana de cada data.

Porém, há uma questão sobre o uso de uma tabela do usuário MASTER, pois ela pode não ser acessível para você. E se não houver possibilidade de obter a devida permissão?

Utilizando uma tabela auxiliar em memória

Um outro método muito eficiente, mas que exige um pouco de Transact-SQL, é usar uma Variável do tipo TABLE. Confira o seguinte exemplo:

DECLARE @T TABLE (NUMBER INT)
DECLARE @I INT, @LIMITE INT

SET @I = 0
SET @LIMITE = 25000
WHILE @I < @LIMITE
BEGIN
    INSERT INTO @T (NUMBER) VALUES (@I)
    SET @I = @I + 1
END

Este código leva menos de 1 segundo para executar e cria uma tabela na memória com números de 0 a 24.999. Usando ainda o exemplo de geração de datas, podemos inserir 25 mil linhas de uma só vez, como mostra o novo exemplo abaixo:

DECLARE @DATAINICIO DATETIME
SET @DATAINICIO = '2001-01-01'
SELECT DATA, DATEPART(DW, DATA)
FROM (SELECT @DATAINICIO + NUMBER DATA FROM @T) T

Conclusão

Já usei várias vezes esse recurso para melhorar o desempenho de cálculos financeiros que envolvem parcelas, justo e vencimentos em feriados. Certa vez consegui diminuir o tempo de execução de uma procedure de cálculos, que levava 20 minutos para inserir 23 mil registros, para cerca de 20 segundos, gerando 150 mil registros.

Embora para alguns os exemplos acima possam soar como algum tipo de gambiarra, o conhecimento e a correta aplicação de tais recursos podem ser essenciaus quando as restrições de desempenho são prioridade.

SQL Server: o porquê do ponto duplo (“..”) para acessar outros bancos

Você já se perguntou por que usa-se um ponto duplo (“..”) para acessar uma tabela de outro banco de dados no SQL Server?

USE BD1
GO
SELECT * FROM BD2..TABELA

Já vi algumas pessoas questionarem e acho que a primeira reação é supor que seja uma espécie de operador especial.

Bem, para entender isso é preciso saber que um objeto (como uma tabela) no SQL Server possui uma identificação assim:

Servidor.BancoDeDados.Schema.Objeto

Com relação ao Schema, as pessoas criam geralmente todos os objetos no Schema padrão, o dbo. Como os objetos estão no mesmo Schema, especificá-lo é opcional.

Mas, quando esquecemos do “.dbo” nos CREATEs e outras DDLs, é bem provável termos problemas no futuro, pois o Schema padrão do usuário utilizado para executar scripts no cliente pode ser diferente. Já vi casos onde metade dos objetos ficavam num Schema e metade em outro.

Então, se você quer acessar uma tabela e está no mesmo servidor, usando (USE) o mesmo banco de dados e o mesmo Schema, poderá omitir todos estes utilizar apenas o nome da tabela:

SELECT * FROM Objeto

Agora vamos supor que você esteja usando (USE) um banco chamado BD1. Como faríamos para acessar uma tabela de um BD2? Seria tão simples como o exemplo abaixo?

USE BD1
GO
SELECT * FROM BD2..Objeto

A resposta é: depende!

Se os dois objetos não estiverem no mesmo Schema, então você deveria especificar o schema entre os dois pontos, assim:

USE BD1
GO
SELECT * FROM BD2.dbo.Objeto

Portanto, os dois pontos (“..”) seguidos significam que você está omitindo o Schema e solicitando ao SQL Server que utilize o padrão para o seu usuário.

Simples assim.

IntelliSense do SQL Server 2008

Se você já usou o SQL Server 2008, já deve ter visto que ele consegue auto-completar nomes de campos e tabelas, além de exibir erros de sintaxe. Este é o “novíssimo” IntelliSense.

O problema é que ele não funciona 100% e tem sérios problemas com cache, pois ao criar novas colunas e tabelas, é difícil fazer ele reconhecer essas alterações.

Vários colegas comentaram esse recurso acaba mais atrapalhando do que ajudando.

Se você compartilha da mesma opinião, fica a dica. Basta clicar no botão da imagem:

Além disso, no menu “Edit > IntelliSense”, você encontra várias opções, como “Refresh Local Cache”, o que significa que ele vai atualizar seu cache local com relação à estrutura da base de dados.

Porém, em alguns testes que fiz, o cache só atualizou mesmo reiniciando o programa… mas quem sabe você pode ter mais sorte!

Cuidado com o ADD_MONTHS do Oracle

Sabe aqueles erros esquisitos onde você olha o código é pensa: “não é possível, tá tudo certo…”?

O cenário

Há muito tempo, trabalhei na migração de diversas procedures de um sistema financeiro de SQL Server para Oracle. Eram procedure complexas com milhares de linhas, muitos cursores e algumas péssimas práticas.

A princípio, a ordem era para migrarmos tudo manualmente. Mas, depois de analisar algumas ferramentas de migração automática, descobri que se fizesse alguns ajustes nas procedures em SQL Server poderia migrá-las inteiramente para Oracle usando o SQL Developer, disponibilizado gratuitamente pela Oracle.

Ao migrar uma procedure, o  SQL Developer utiliza algumas rotinas de adaptação para o Oracle, que deve estar disponível num package chamado qlserver_utilites. Fiz um parser simples que substituía as chamadas a esse package por funções nativas do Oracle, evitando criar código desnecessário e que diminuiria a performance.

Escrevi um documento com os ajustes necessários e se tornou o padrão do projeto. Assim conseguimos manter o código-fonte único.

Entretanto, identificamos que algumas operações específicas do sistema geraram lançamentos com valores estranhos, mas a maioria estava correta. No caso, o cálculo envolvia períodos de datas e a primeira observação é que a data inicial do período era dia 30, o último do mês.

Cálculos daqui, cálculos dali, verificou-se que, apenas para esse tipo de período, a procedure estava calculando um dia a mais numa diferença entre as datas. Uma dessas datas era calculada somando-se um número X de meses.

A origem do problema

No Oracle, a rotina usada para somar datas é ADD_MONTHS. Seria o equivalente de DATEADD do SQL Server.

Entretanto, essa rotina segue um padrão ANSI muito esquisito:  se a data é o último dia do mês, ao somar X meses, o resultado será sempre no último dia do mês.

Vamos supor que a data fosse 28/02, ao somar um mês, o resultado seria 31/03, a não ser no ano bissexto. Para 30/04, por exemplo, somando-se um mês, o resultado seria 31/05.

Isso estava gerando a distorção nos cálculos.

Solução

Foi necessário criar uma function para somar meses e que mantivesse o dia fixo, a não ser quando o mês do resultado tinha menos dias  do que o mês original.

Conclusões

Não confiar na implementação de uma rotina somente pelo nome, principalmente entre softwares tão diferentes.

No caso de migração, analisar a fundo a equivalência de funções, métodos, classes, etc. Se possível, com testes unitários.

Script de Geração de Carga de Dados (Oracle e SQL Server)

No dia-a-dia do desenvolvimento, é comum precisarmos reproduzir uma determinada situação ocorrida no cliente. Só que precisamos dos dados exatos para isso!

Geralmente solicitamos SELECTs ou backups. Mas e quando não dá?

Em ambientes com restrições onde não é possível solicitar um backup e um SELECT não é suficiente, pois precisamos trabalhar com os dados e não apenas visualizá-los, existe uma alternativa prática para recuperar os dados do ambiente.

Script que gera INSERTs

Inicialmente desenvolvi um script para Oracle em PL/SQL para migração de dados entre ambientes. O resultado foi bom e fizemos também uma versão para SQL Server.

No caso do SQL Server trata-se de uma procedure que, quando executada, gera INSERTS a partir dos dados de uma tabela. Veja os exemplos abaixo:

-- Gera INSERTS para todos os registros da tabela TAB1
EXEC GET_INSERT_SCRIPT
    @TABELA = 'TAB1',
    @BANCO_ORIGEM = 'MINHA_BASE',
    @BANCO_DESTINO = DEFAULT,
    @OWNER = DEFAULT,
    @WHERE = DEFAULT,
    @GERAR_DELETE = 0

-- Gera INSERTs para todos os registros da tabela TAB2
EXEC GET_INSERT_SCRIPT
    @TABELA = 'TAB2',
    @GERAR_DELETE = 0

-- Gera INSERT para a tabela de produtos somente para o produto 'PROD' da empresa 'EMP1'
EXEC GET_INSERT_SCRIPT
    @TABELA = 'PRODUTOS',
    @WHERE = 'CODPRODUTO = ''PROD'' AND LECOLCOD = ''EMP1'' '

-- Gera INSERT da tabela BOLETOS somente para o boleto '123'
EXEC GET_INSERT_SCRIPT
    @TABELA = 'BOLETOS',
    @BANCO_ORIGEM = 'MINHA_BASE',
    @BANCO_DESTINO = DEFAULT,
    @OWNER = DEFAULT,
    @WHERE = 'CODBOLETO = 123'

Observações

No primeiro exemplo, é definido o banco de origem como  “MINHA_BASE”. Esse parâmetro é opcional, se o banco de dados já estiver selecionado (“USE”).

Os parâmetros com valor “DEFAULT” indicam que o SQL deve usar o valor padrão definido pela procedure. Eles poderiam ser simplesmente omitidos.

O parâmetro @GERAR_DELETE define se o script gerado vai conter também um comando “DELETE” para apagar os dados da tabela.

Os parâmetros @OWNER, @BANCO_ORIGEM e @BANCO_DESTINO podem ser omitidos e somente precisam ser usados se houver necessidade de executar os scripts a partir do master ou com um usuário cujo owner é diferente do owner  do banco de dados. O padrão para o @OWNER é “dbo”.

Caso esses parâmetros sejam informados, o caminho para os objetos será escrito como no exemplo:

AB_BANCO_DESTINO.dbo.TABELA

No segundo exemplo, o parâmetro @WHERE faz com que os dados gerados e o DELETE (se houver) contenham o filtro passado. Então também é possível criar uma carga parcial com o filtro desejado.

Somente é preciso tomar cuidado com a saída. O Management Studio limita a quantidade de texto retornado, então é recomendável alterar a configuração  Tools > Options > Query Results > SQL Server > Results to Text > Maximum number of characters... para o valor de “4000” ou simplesmente jogar a saída para um arquivo.

Vantagens

Armazenamento e migração de dados de forma transparente, sendo possível analisar e alterar os dados.

O desenvolvedor pode selecionar exatamente os registros que deseja. São poucas as ferramentas que permitem geração de scripts com filtro por registros.

Não é necessário que a pessoa que vai executá-lo saiba usar uma ferramenta de geração de scripts e tenha que selecionar as tabelas ou até registros manualmente.

Problemas

Tabelas com tipos de dados que não podem ser representados em texto.

Tabelas muitos grandes vão gerar scripts impossíveis de editar.

Versão para Oracle

A versão Oracle atual não tem todas as opções da versão SQL Server, mas já foi utilizada para migração de dados entre ambientes com sucesso.

Disponível em

https://github.com/utluiz/database-insert-script-generator

Dúvidas, sugestões, correções ou apontamento de erros são bem-vindos!

Data loading script generator (Oracle and SQL Server)

In software development, there’s a common scenario where we need to transport data between separated environments.

For instance, we routinely have to reproduce customer’s problems. In order to do that, we need the same data. Sometimes we just enter the production environment or request a backup, but in a few cases we aren’t allowed to do that! Some environments have security constraints that makes it impossible to obtain a backup, for example. We can request a query, but it isn’t proper because we want to work with the data.

How we can accomplish that?

Script that generates INSERTs

At first I developed an Oracle PL/SQL script to migrate data between different environments. It worked well so I did a SQL Server T-SQL version too.

Indeed, I’m talking about a procedure that generates INSERT commands from table data. Check out the following examples:

-- Generates INSERTS for all records from table TAB1
EXEC GET_INSERT_SCRIPT
    @TABELA = 'TAB1', -- table name
    @BANCO_ORIGEM = 'MY_DATABASE', -- source database
    @BANCO_DESTINO = DEFAULT, -- target database
    @OWNER = DEFAULT,
    @WHERE = DEFAULT,
    @GERAR_DELETE = 0 -- generate DELETE command?

-- Generates INSERTs for all records from table TAB2
EXEC GET_INSERT_SCRIPT
    @TABELA = 'TAB2', -- table name
    @GERAR_DELETE = 0 -- generate DELETE command?

-- Generates INSERTs for product table, but only when code is 'PROD' and company is 'COMP1'
EXEC GET_INSERT_SCRIPT
    @TABELA = 'PRODUCT', -- table name
    @WHERE = 'ID = ''PROD'' AND ID_COMPANY = ''COMP1'' '

--Generates INSERTs for client table when client code is 123
EXEC GET_INSERT_SCRIPT
    @TABELA = 'CLIENT', -- table name
    @BANCO_ORIGEM = 'MY_DATABASE', -- source database
    @BANCO_DESTINO = DEFAULT, -- target database
    @OWNER = DEFAULT,
    @WHERE = 'ID = 123'

Remarks

In the first example, we set the source database as MY_DATABASE. This parameter is optional if the database is already selected with USE command.

The parameters with DEFAULT value instructs SQL Server to use the default value defined in procedure signature. They could be omitted.

The parameter @GERAR_DELETE defines if the generated script will have a DELETE command to remove the table data before the insertions.

The parameters @OWNER, @BANCO_ORIGEM, @BANCO_DESTINO can also be omitted and only need to be informed if you execute the script from master or from a different database owner. The default owner is dbo.

If these parameters are informed, the “path” to the database objects will be like this:

TARGET_DATABASE.dbo.TABLE

In the second example, the parameter @WHERE adds a filter clause to the generated data and DELETE command. This way we can load data selectively.

We only need be cautious with the output. Management Studio limits the text output size, so my advice is to change the configuration Tools > Options > Query Results > SQL Server > Results to Text > Maximum number of characters... to 4000 (maximum characters per line) or just output to file.

Advantages

  • Store and migrate data in a clear and simple way.
  • Easily see and change all data.
  • Select exactly the data needed. Many tools don’t give you the option to export data selectively.
  • It ins’t necessary deploy any tool. The person who will generate the scripts just need to run a single script.

Potential problems

  • Tables with data types that cannot be represented as text cannot be generated.
  • Large tables will generate huge scripts hard to open in an editor.

Oracle version

The Oracle version haven’t all features I implemented in SQL Server, but as I told you in the beginning, it was successfully used to migrate data between different environments.

Available in

https://github.com/utluiz/database-insert-script-generator

Questions, suggestions, and bug fixes are welcome!

Creative Commons O blog State of the Art de Luiz Ricardo é licenciado sob uma Licença Creative Commons. Copie, compartihe e modifique, apenas cite a fonte.