Arquivo da categoria: Scripts

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

Publicado em por

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

Publicado em por

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

Publicado em por

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

Publicado em por

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?

Publicado em por

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

Publicado em por

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:

Script de Cidades e Estados [Update]

Publicado em por
Postei uma vez este link no fórum do MSDN e teve um bom resultado, trata-se de um script SQL para popular tabelas de Cidade, Estado e Pais no SQL Server, para mudar a estrutura do script nada melhor que o Regular Expression do Replace do SQL Server Management Studio (Já fiz isto muitas vezes, pois muitos projetos que participei tinham estrutas de tabelas de cidade/estado diferentes ou até mesmo banco de dados diferentes, como o MySQL, Interbase e Oracle). Recomendo também dar uma olhada nos arquivos, que como este, estão no meu Virtual Disc, pois sempre estou colocando arquivos úteis lá, como os materiais do Student To Business (FY 2009).
Download do Script

[2011-03-29] Problema com a limitação do 4shared fez com que meus arquivos compartilhados fossem enviados para o limbo (…)

[2011-04-01] Estou disponibilizando pelo mediafire um novo script SQL de Cidades e Estados:

http://www.mediafire.com/?2z0xfdhqmh2ysm3

[2011-04-02] Atendendo aos pedidos, também estou disponibilizando um arquivo CSV com a relação UF/Cidades:

http://www.mediafire.com/?mzx1467rlkyafkn

Script de Cidades e Estados

Publicado em por
Postei uma vez este link no fórum do MSDN e teve um bom resultado, trata-se de um script SQL para popular tabelas de Cidade, Estado e Pais no SQL Server, para mudar a estrutura do script nada melhor que o Regular Expression do Replace do SQL Server Management Studio (Já fiz isto muitas vezes, pois muitos projetos que participei tinham estrutas de tabelas de cidade/estado diferentes ou até mesmo banco de dados diferentes, como o MySQL, Interbase e Oracle). Recomendo também dar uma olhada nos arquivos, que como este, estão no meu Virtual Disc, pois sempre estou colocando arquivos úteis lá, como os materiais do Student To Business (FY 2009).
Download do Script

[2011-03-29] Problema com a limitação do 4shared fez com que meus arquivos compartilhados fossem enviados para o limbo (…)

[2011-04-01] Estou disponibilizando pelo mediafire um novo script SQL de Cidades e Estados:

http://www.mediafire.com/?2z0xfdhqmh2ysm3

[2011-04-02] Atendendo aos pedidos, também estou disponibilizando um arquivo CSV com a relação UF/Cidades:

http://www.mediafire.com/?mzx1467rlkyafkn