Verificar a versão dos dados de uma tabela por CHECKSUM

Olá pessoas!

Recentemente, encontrei um script bem interessante para realizar o CHECKSUM de todos os dados de todas as colunas de uma tabela, fiz algumas alterações mínimas para reduzir o código do script original, e ficou desta forma:

DECLARE @schema_name sysname
SET @schema_name = 'dbo'

DECLARE @table_name sysname
SET @table_name = 'Nome da tabela'

DECLARE @column_list VARCHAR(MAX)

SELECT @column_list =
	COALESCE(@column_list + ', ', '') + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
AND TABLE_SCHEMA = @schema_name

DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT CHECKSUM_AGG(CHECKSUM({@column_list})) FROM [{@schema_name}].[{@table_name}]'

SET @sql = REPLACE(@sql, '{@column_list}', @column_list)
SET @sql = REPLACE(@sql, '{@schema_name}', @schema_name)
SET @sql = REPLACE(@sql, '{@table_name}', @table_name)

EXEC (@sql)

Esta consulta retorna o CHECKSUM de todos os dados da tabela de forma rápida (na maioria das vezes), e permite verificar por meio deste CHECKSUM se a tabela foi ou não alterada, sendo uma ótima alternativa comparar alterações em tabelas similares em bancos distintos e criar uma estrutura simples de versionamento.

A única limitação que encontrei para este script é a necessidade de um tratamento diferenciado para colunas XML:

Msg 8116, Level 16, State 4, Line 1
Argument data type xml is invalid for argument X of checksum function.

Referências

Script original:
http://stackoverflow.com/questions/1560306/calculate-hash-or-checksum-for-a-table-in-sql-server

Função CHECKSUM:
http://msdn.microsoft.com/en-us/library/ms189788.aspx

Função CHECKSUM_AGG:
http://msdn.microsoft.com/en-us/library/ms188920.aspx

Artigos relacionados:

Comparando a estrutura de tabelas diferentes
https://sqlfromhell.wordpress.com/2010/01/09/query-from-hell-comparando-a-estrutura-de-tabelas-diferentes/

Red Gate – Overview – SQL Data Compare
https://sqlfromhell.wordpress.com/2011/05/23/red-gate-overview-sql-data-compare/

Visual Studio 2010 – Data Compare
https://sqlfromhell.wordpress.com/2011/01/27/visual-studio-2010-data-compare/

Script SQL de cidades e estados do Brasil, Argentina e Estados Unidos

Um problema chato quando se inicia muitos projetos internacionais é o fato de ter que ficar caçando cidades/estados dos países de atuação do projeto. Pelo menos para resolver esta questão estou disponibilizando alguns links para os scripts que encontrei pela internet:

Scripts de Cidades e Estados do Brasil:
https://sqlfromhell.wordpress.com/2009/05/21/cidades-e-estados/
(CSV e SQL originalmente para SQL Server)

Scripts de Cidades e Estados da Argentina:
http://kerzek.blogspot.com/2008/04/script-sql-de-provincias-y-localidades.html
(SQL originalmente para SQL Server, mas é possível migrar para outro banco de dados com um pouco de trabalho)

Scripts de Cidades e Estados dos Estados Unidos:
http://www.farinspace.com/us-cities-and-state-sql-dump/
(Originalmente para MySQL, mas é possível adaptar para outros bancos de dados)

Para uma relação mais geral de cidades por país:
http://geolite.maxmind.com/download/worldcities/
(Formato compatível com CSV, os países são identificados por ISO 3166)

Quem tiver outras fontes de dados e quiser compartilhar, fique a vontade para colocar os links destas fontes nos comentários.

Gerando templates das System Views e Information Functions

Boa noite pessoas, para limpar minha pasta de scripts, segue outras duas queries que ainda não tinha colocado no blog.

Gerando templetes das System Views:

SELECT
[name] AS [View]
,'SELECT * FROM [sys].['+ [name] + ']' AS Query
FROM sys.all_views
WHERE [schema_id] = SCHEMA_ID('sys')
ORDER BY [name]

Gerando templates das Information Functions:

SELECT
[name] AS [Function]
,'SELECT * FROM [sys].['+ [name] + ']'
+ ISNULL('(' + LEFT([parameters], LEN([parameters]) - 1) + ')', '')
AS Query
FROM sys.all_objects o
CROSS APPLY
(
SELECT CAST((
SELECT [name] + ', '
FROM [sys].[all_parameters]
WHERE [object_id] = o.[object_id]
FOR XML PATH('')
) AS VARCHAR(8000)) AS [parameters]
) p
WHERE [schema_id] = SCHEMA_ID('sys')
AND [type] = 'IF'
ORDER BY [name]

Relacionando as colunas de cada tabela no SQL Server

Outras consultas que me pediram a aproximadamente um ano atrás e não publiquei no site, recuperam uma lista das colunas de cada tabela do banco de dados do SQL Server.

Basicamente, podemos utilizar a view INFORMATION_SCHEMA.COLUMNS, ou montar a nossa própria consulta com sys.columns:

SELECT s.name as [schema], t.name as [table], c.name AS [column]

FROM sys.schemas s
INNER JOIN sys.tables t
ON s.[schema_id] = t.[schema_id]

INNER JOIN sys.columns c
ON t.[object_id] = c.[object_id]

Ou

SELECT TABLE_SCHEMA as [schema]
, TABLE_NAME as [table]
, COLUMN_NAME as [column]

FROM [INFORMATION_SCHEMA].[COLUMNS]

Exemplo de execução:

Para trazer a relação de todas as colunas de cada tabela em uma só linha, precisaremos de um pouco de criatividade com FOR XML e CROSS APPLY:

SELECT s.name as [schema], t.name as [table]

, SUBSTRING(c.[columns], 0, LEN(c.[columns])) AS [columns]

FROM sys.schemas s
INNER JOIN sys.tables t
ON s.[schema_id] = t.[schema_id]

-- Relação de colunas [columns]
CROSS APPLY (
	SELECT (
		SELECT c.name + ', '
		FROM sys.columns c
		WHERE t.[object_id] = c.[object_id]
		FOR XML PATH('')
	) AS [columns]
) AS c

Exemplo de execução:

Quantos índices, colunas e linhas tem cada tabela?

Me pediram a quase um ano atrás uma consulta simples que recuperasse as informações de quantos índices, colunas e linhas existem em cada tabela de um banco de dados do SQL Server, também se os registros estavam armazenado em HEAP ou CLUSTERED.

Na época, eu fiz a consulta, mas esqueci de compartilhar aqui no site, espero que gostem dela:

SELECT s.name as [schema], t.name as [table]
, ix.[indexes]
, c.[columns]
, p.[rows]
, i.[type_desc]
FROM sys.schemas s
INNER JOIN sys.tables t
	ON t.[schema_id] = s.[schema_id]

-- Contagem de linhas [rows]
INNER JOIN sys.indexes i
	ON i.[object_id] = t.[object_id]

INNER JOIN sys.partitions p
	ON p.[object_id] = t.[object_id]
	AND p.[index_id] = i.[index_id]

-- Contagem de colunas [columns]
CROSS APPLY (
	SELECT COUNT(*) AS [columns]
	FROM sys.columns c
	WHERE c.[object_id] = t.[object_id]
) c

-- Contagem de índices [indexes]
CROSS APPLY (
	SELECT COUNT(*) AS [indexes]
	FROM sys.indexes ix
	WHERE ix.[object_id] = t.[object_id]
	AND ix.type NOT IN (0,1)
) ix

WHERE i.[type] IN (0,1)

ORDER BY [schema], [table]

Exemplo de execução:

Imagens no banco de dados 5 – Importando arquivos por T-SQL

Para finalizar este série de artigos sobre técnicas de como trabalhar com imagens/arquivos no SQL Server, vamos utilizar o próprio T-SQL para realizar a importação de arquivos para dentro do banco de dados.

Pareceu difícil? Não se preocupem, é bem tranquilo.

O primeiro comando deste artigo utiliza a função OPENROWSET, especificando o caminho do arquivo, para recuperar os bytes deste arquivo em formato VARBINARY:

SELECT
	Imagem.BulkColumn AS [Bytes]
FROM
	OPENROWSET(BULK N'C:\cake.jpg', SINGLE_BLOB) AS Imagem

Para um INSERT na tabela que temos trabalhado nos artigos anteriores, basta utilizar uma instrução de INSERT..SELECT:

INSERT INTO [DemoApp].[dbo].[Arquivos]
	([Nome]
	,[Bytes]
	,[Extensao]
	,[Tipo])
SELECT
	'cake.jpg' AS [Nome],
	Imagem.BulkColumn AS [Bytes],
	'.jpg' AS [Extensao],
	'image/jpeg' AS [Tipo]
FROM
	OPENROWSET(BULK N'C:\cake.jpg', SINGLE_BLOB) AS Imagem

No caso de operações que exigiam a importação de vários arquivos de uma determinada pasta, será necessário utilizar da Stored Procedure xp_cmdshell, que necessita executar as operações a seguir para ser habilitada:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

(Lembrando que por questões de segurança, ao termino da utilização da stored procedure xp_cmdshell, desabilitem-na)

E por meio desta Stored Procedured, poderemos relacionar todos os arquivos de uma determinada pasta do servidor de banco de dados:

DECLARE @T TABLE ( CMD NVARCHAR(255))

INSERT INTO @T
EXEC xp_cmdshell 'dir E:\Backup\Pictures\2010\Sukiaki\*.jpg /p';

SELECT SUBSTRING(CMD, 40, 215)
FROM @T
WHERE CMD LIKE '%.jpg'
GO

Por fim, unimos a ideia do INSERT..SELECT do OPENROWSET e a execução da Stored Procedure xp_cmdshell:

DECLARE @T TABLE ( CMD NVARCHAR(255))

DECLARE @PATH NVARCHAR(255)
SET @PATH = 'E:\Backup\Pictures\2010\Sukiaki\'

DECLARE @CMD NVARCHAR(255)
SET @CMD = 'dir ' + @PATH + '*.jpg /p'

INSERT INTO @T
EXEC xp_cmdshell @CMD;

DECLARE Arquivos CURSOR FOR
SELECT
	SUBSTRING(CMD, 40, 215) AS Arquivo
FROM @T
WHERE CMD LIKE '%.jpg';

OPEN Arquivos;

DECLARE @Arquivo NVARCHAR(255)
DECLARE @ArquivoPath NVARCHAR(255)

FETCH NEXT FROM Arquivos INTO @Arquivo;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @ArquivoPath = @PATH + @Arquivo;

	EXEC (
		'INSERT INTO [DemoApp].[dbo].[Arquivos]
			([Nome]
			,[Bytes]
			,[Extensao]
			,[Tipo])
		SELECT
			''' + @Arquivo + ''' AS [Nome],
			Imagem.BulkColumn AS [Bytes],
			''.jpg'' AS [Extensao],
			''image/jpeg'' AS [Tipo]
		FROM
			OPENROWSET(BULK ''' + @ArquivoPath + ''', SINGLE_BLOB)
				AS Imagem'
	)

	FETCH NEXT FROM Arquivos INTO @Arquivo;
END

CLOSE Arquivos;

DEALLOCATE Arquivos;

E teremos importado todos os arquivos: