Red Gate – Overview

Acredito que é praticamente impossível existir um DBA de SQL Server que nunca tenha ouvido falar dos produtos da Red Gate Software. Mas vamos a um overview de alguns produtos que considero interessantes, e nos próximos artigos, vou demonstrar o funcionamento destas ferramentas.

SQL Prompt Pro:
Oferece recursos como o intellisense de T-SQL, snippets, formatação do script, entre outras funcionalidades. Totalmente integrado com o SQL Server Management Studio e possui algumas funções também integradas com o Visual Studio.

O que é intellisense e snippets? Intellisense é aquele “autocomplete” que quando você escreve “SELECT * FROM” e já aparecem os nomes das tabelas do banco de dados para você selecionar. Snippets é aquele recurso que permite configurar atalhos para alguns comandos, exemplo, escrever “sl” e já aparecer “SELECT”.

SQL Compare Pro (também para Oracle):
Basicamente permite comparar a estrutura de dois bancos de dados e gerar um script para sincronizar a estrutura deles, além de outras operações mais complexas.

A função básica desta ferramenta é semelhante ao Schema Compare do Visual Studio, mas com muito mais possibilidades e recursos (comparação de arquivos de backup, snapshot, scripts).

SQL Data Compare Pro (também para Oracle):
Permite comparar a massa de dados de dois bancos de dados, e gerar o script para sincronizar esta massa de dados, também oferecendo recursos para outras operações mais complexas.

A função básica desta ferramenta é semelhante ao Data Compare do Visual Studio, mas com muito mais possibilidades e recursos.

Considerações finais

Também há várias outras ferramentas para SQL Server, como o SQL Source Control (controle de versão de documentos e script para SQL Server), SQL Monitor (monitor de desempenho e alertas) e SQL Doc (documentação do banco de dados), e outras para Visual Studio, Oracle e Exchange.

Mais informações:
http://www.red-gate.com/

Anúncios

Existem vulnerabilidades no seu SGBD?

Para avaliar um software na perspectiva de segurança e vulnerabilidades, temos um site de bastante credibilidade para obter informação nesta prespectiva, o Secunia.com, que possui uma relação de milhares de softwares e quantitativos de vulnerabilidades.

Abaixo alguns SGBDs comuns relacionados por este site:

IBM DB2 9.x
http://secunia.com/advisories/product/13504/

Oracle 11.x
http://secunia.com/advisories/product/18050/

Microsoft SQL Server 2008
http://secunia.com/advisories/product/21744/

MySQL 5.x
http://secunia.com/advisories/product/8355/

PostgreSQL 8.x
http://secunia.com/advisories/product/4587/

E um menos comum, mas que eu já tive oportunidade de trabalhar:

Caché 5.x
http://secunia.com/advisories/product/1808/

FUNÇÕES: Validação de CNPJ e CPF com T-SQL

[2011-04-02] Código completo:

DECLARE @TEXTO VARCHAR(20)
SET @TEXTO = '02.841.834/0001-55'

DECLARE @CPF_CNPJ VARCHAR(20)
SET @CPF_CNPJ = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @CPF_CNPJ += LETRA
FROM SPLIT
WHERE LETRA LIKE '[0-9]'
OPTION(MAXRECURSION 0)

IF LEN(@CPF_CNPJ) NOT IN (11, 14)
BEGIN
	SELECT 'Inválido'
	RETURN
END

DECLARE
	@DIGITO1 INT,
	@DIGITO2 INT,
	@VALOR1 INT,
	@VALOR2 INT

DECLARE
	@I INT,
	@J INT,
	@TOTAL_TMP INT,
	@COEFICIENTE_TMP INT,
	@DIGITO_TMP INT,
	@VALOR_TMP INT

SET @DIGITO1 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1)
SET @DIGITO2 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1)
SET @J = 1

WHILE @J <= 2 BEGIN 	SELECT 		@TOTAL_TMP = 0, 		@COEFICIENTE_TMP = 2 	SET @I = ((LEN(@CPF_CNPJ) - 3) + @J) 	WHILE @I >= 0
	BEGIN
		SELECT
			@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
			@TOTAL_TMP = @TOTAL_TMP + (@DIGITO_TMP * @COEFICIENTE_TMP),
			@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1

		IF (@COEFICIENTE_TMP > 9) AND LEN(@CPF_CNPJ) = 14
			SET @COEFICIENTE_TMP = 2
		SET @I = @I - 1
	END

	SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)

	IF (@VALOR_TMP >= 10)
		SET @VALOR_TMP = 0

	IF @J = 1
		SET @VALOR1 = @VALOR_TMP
	ELSE
		SET @VALOR2 = @VALOR_TMP
	SET @J = @J + 1
END

SELECT
	CASE WHEN @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
		THEN 'Válido'
		ELSE 'Inválido'
	END

[Post-Original] Explicação:

Boa noite pessoas!

No blog do Rogério, encontrei um ótimo algoritmo de validação de CNPJ e CPF para ORACLE, para que ninguém tenha que ficar transitando de ‘PL\SQL’ para ‘T-SQL’, já realizei este trabalho e vou detalhar um pouco o funcionamento deste algoritmo.

Primeiro precisamos de um CNPJ ou CPF para validar, assim identifiquei um que estava “vagando” na internet.

DECLARE @CPF_CNPJ VARCHAR(20)
SET @CPF_CNPJ = '02.841.834/0001-55'

Ok, tenho o CNPJ/CPF para validar, mas tenho que remover os caracteres não numéricos, desta forma utilizarei a função SOMENTE_NUMEROS que desenvolvi para o artigo anterior:

SET @CPF_CNPJ = dbo.SOMENTE_NUMEROS(@CPF_CNPJ)

A primeira valiação verifica se a quantidade de caracteres é de um CPF ou de um CNPJ

IF LEN(@CPF_CNPJ) NOT IN (11, 14)
BEGIN
SELECT 'Inválido'
RETURN
END

Em sequida, defino quatro variáveis que serão utilizadas para a validação dos digitos verificadores do CNPJ e CPF.

DECLARE
@DIGITO1 INT,
@DIGITO2 INT,
@VALOR1 INT,
@VALOR2 INT

Também há outras variáveis a serem utilizadas durante a operação.

DECLARE
@I INT,
@J INT,
@TOTAL_TMP INT,
@COEFICIENTE_TMP INT,
@DIGITO_TMP INT,
@VALOR_TMP INT

As variáveis DIGITO1 e DIGITO2 armazenarão os digitos verificadores do CNPJ/CPF.

SET @DIGITO1 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1)
SET @DIGITO2 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1)

Feito isso, determino um laço de repetição para dois loops, um para cada digito.

SET @J = 1
WHILE @J <= 2
BEGIN

Determino os valores inicias das variáveis de validação.

SELECT
@TOTAL_TMP = 0,
@COEFICIENTE_TMP = 2

Agora, aplico o algoritmo para gerar cada um dos digitos verificadores.

SET @I = ((LEN(@CPF_CNPJ) - 3) + @J)
WHILE @I >= 0
BEGIN
SELECT
@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
@TOTAL_TMP = @TOTAL_TMP + (@DIGITO_TMP * @COEFICIENTE_TMP),
@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1
IF (@COEFICIENTE_TMP > 9) AND LEN(@CPF_CNPJ) = 14
SET @COEFICIENTE_TMP = 2
SET @I = @I - 1
END
 SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)
IF (@VALOR_TMP >= 10)
SET @VALOR_TMP = 0

Identifico qual dos digitos verificadores foi gerado e prossigo para o próximo digito no laço de repetição.

IF @J = 1
SET @VALOR1 = @VALOR_TMP
ELSE
SET @VALOR2 = @VALOR_TMP
SET @J = @J + 1
END

Por fim, verifico se os digitos gerados têm o mesmo valor dos presente no CNPJ/CPF.

IF @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
SELECT 'Válido'
ELSE
SELECT 'Inválido'

Para o próximo artigo, ainda estou estudando uma forma interessante de trabalhar com máscaras e CTE.

Artigos relacionados:

Blog do Rogério – Validação CNPJ/CPF com PL/SQL
http://blogdozunga.blogspot.com/2009/02/precisava-de-uma-rotina-para-validar.html

FUNÇÕES: Somente Números (com CTE)
https://sqlfromhell.wordpress.com/2009/11/18/somente-numeros-com-cte/

FUNÇÕES: Somente Números (com WHILE)
https://sqlfromhell.wordpress.com/2009/11/17/somente-numeros-com-while/

Connection Strings

“Problemas com Connection Strings, nós temos a solução!”, isso parece bobagem, mas parece ser a idéia do site ConnectionStrings.com, achei que era brincadeira, mas é sério, eles ressucitaram até o Paradox e o Informix!!! Abaixo a lista de links para as connection strings: Continuar lendo

Queries para dicionário de dados – Step 2

Continuando o post de Dicionário de dados,

Desde versões antigas do SQL Server são encontradas system view que permitem de várias formas mapear as entidades do banco de dados, desde relacionar as colunas de uma determinada tabela até mapear relacionamentos, índices e arquivos de armazenamento do banco de dados.

Primeiramente, como encontrar os relacionamentos no SQL Server 2000 e superiores:

SELECT
  OBJECT_NAME(foreigns.rkeyid) Parent_Table,
  OBJECT_NAME(foreigns.fkeyid) Child_Table,
  OBJECT_NAME(foreigns.constid) Key_Name,
  parent_columns.name Parent_Col,
  child_columns.name Child_Col
FROM sys.sysforeignkeys foreigns
INNER JOIN sys.syscolumns parent_columns
  ON foreigns.fkeyid = parent_columns.id
  AND foreigns.fkey = parent_columns.colid
INNER JOIN sys.syscolumns child_columns
  ON foreigns.rkeyid = child_columns.id
  AND foreigns.rkey = child_columns.colid
ORDER BY Parent_Table, Child_Table

A mesma funcionalidade no SQL Server 2005/2008:

SELECT
  OBJECT_NAME(foreigns_columns.[referenced_object_id]) Parent_Table,
  OBJECT_NAME(foreigns_columns.[parent_object_id]) Child_Table,
  OBJECT_NAME(foreigns_columns.[constraint_object_id]) Key_Name,
  parent_columns.name Parent_Col,
  child_columns.name Child_KeyCol
FROM sys.foreign_key_columns foreigns_columns
INNER JOIN sys.columns parent_columns
  ON parent_columns.[object_id] = foreigns_columns.[parent_object_id]
  AND parent_columns.[column_id] = foreigns_columns.[parent_column_id]
INNER JOIN sys.columns child_columns
  ON child_columns.[object_id] = foreigns_columns.[referenced_object_id]
  AND child_columns.[column_id] = foreigns_columns.[referenced_column_id]
ORDER BY Parent_Table, Child_Table

A mesma funcionalidade sem o uso da função “OBJECT_NAME”:

SELECT
  parent.name Parent_Table,
  child.name Child_Table,
  foreigns.name FKey_Name,
  parent_columns.name Parent_Col,
  child_columns.name Child_Col
FROM sys.tables parent
INNER JOIN sys.foreign_keys foreigns
  ON parent.[object_id] = foreigns.[referenced_object_id]
INNER JOIN sys.tables child
  ON child.[object_id] = foreigns.[parent_object_id]
INNER JOIN sys.foreign_key_columns foreigns_columns
  ON foreigns_columns.[constraint_object_id] = foreigns.[object_id]
INNER JOIN sys.columns parent_columns
  ON parent_columns.[object_id] = foreigns_columns.[parent_object_id]
  AND parent_columns.[column_id] = foreigns_columns.[parent_column_id]
INNER JOIN sys.columns child_columns
  ON child_columns.[object_id] = foreigns_columns.[referenced_object_id]
  AND child_columns.[column_id] = foreigns_columns.[referenced_column_id]
ORDER BY Parent_Table, Child_Table

Agora, para qualquer banco de dados ANSI (MySQL, SQL Server, SQL Lite, PostgreSQL, Oracle), utilizando as system views do INFORMATION_SCHEMA:

SELECT
  KU.TABLE_NAME Parent_Table,
  KU2.TABLE_NAME Child_Table,
  FK.CONSTRAINT_NAME FKey_Name,
  KU.COLUMN_NAME Parent_Col,
  KU2.COLUMN_NAME Child_Col
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KU
  ON KU.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KU2
  ON KU2.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
  AND KU.ORDINAL_POSITION = KU2.ORDINAL_POSITION

Queries para dicionário de dados – Step 1

Olá pessoal,

Uma coisa complicada quando se modela ou dá suporte a um bancos de dados, é entender a sua estrutura, para isso pensa-se em documentá-los com o Diagrama de Modelo de Entidades e Relacionamentos (DER ou MER) e/ou com o Dicionário de Dados (DD).

Na categoria Visio do Blog (at. https://sqlfromhell.wordpress.com/category/visio/ ) existe uma série de post descrevendo como fazer o Diagrama de Modelo de Entidades e Relacionamentos (DER ou MER), e no próprio Visio também pode ser feito o Dicionário de Dados (DD).

Como nem todos utilizam ou têm acesso ao Visio, neste post vou relacionar as queries utilizadas para consultar a relação de Tabelas, Colunas e Tipos de dados existentes no SQL Server 2000 ao 2008, que são úteis para realizar outras atividades, como “geradores de código” e “relatórios dinâmicos”.

Primeiramente, para relacionar as tabelas do SQL Server podemos fazer uso de system views para SQL Server 2005 e superiores:

SELECT * FROM sys.tables

ou para SQL Server 2000 e superiores:

SELECT * FROM sysobjects
WHERE xtype LIKE 'U'

Para relacionar as tabelas e suas respectivas colunas para SQL Server 2005 e superiores:

SELECT
  T.name as Tabela,
  C.name as Coluna
FROM sys.tables T
INNER JOIN sys.columns C
  ON C.object_id = T.object_id

 ou para SQL Server 2000 e superiores:

SELECT
  T.name as Tabela,
  C.name as Coluna
FROM sysobjects T
INNER JOIN syscolumns C
  ON T.id = C.id
  AND T.xtype LIKE 'U' -- Verifica se o tipo de objeto é uma tabela

Agora uma query from hell para relacionar as colunas ao seus respectivos tipos para SQL Server 2005 e superiores:

SELECT
  T.name as Tabela,
  C.name as Coluna,
  TY.name as Tipo,
  C.max_length, -- Tamanho em bytes, para nvarchar normalmente se divide este valor por 2
  C.precision, -- Para tipos numeric e decimal (tamanho)
  C.scale -- Para tipos numeric e decimal (números após a virgula)
FROM sys.columns C
INNER JOIN sys.tables T
  ON T.object_id = C.object_id
INNER JOIN sys.types TY
  ON TY.user_type_id = C.user_type_id
ORDER BY T.name, C.name

ou para SQL Server 2000 e superiores:

SELECT
  T.name as Tabela,
  C.name as Coluna,
  TY.name as Tipo,
  C.length, -- Tamanho em bytes, para nvarchar normalmente se divide este valor por 2 ou utiliza a 'prec'
  C.prec, -- Para tipos numeric e decimal (tamanho)
  C.scale -- Para tipos numeric e decimal (números após a virgula)
FROM syscolumns C  
INNER JOIN sysobjects T
  ON T.id = C.id
  AND T.xtype LIKE 'U' -- Verifica se o tipo de objeto é uma tabela
INNER JOIN systypes TY
  ON C.usertype = TY.usertype
  AND C.xtype = TY.xtype
ORDER BY T.name, C.name

Agora, para qualquer banco de dados ANSI (MySQL, SQL Server, SQL Lite, PostgreSQL, Oracle), utilizando as system views do INFORMATION_SCHEMA, relacionando as colunas e seus respectivos tipos e tabelas:

SELECT
 TABLE_NAME as Tabela,
 COLUMN_NAME as Coluna,
 DATA_TYPE as Tipo,
 CHARACTER_MAXIMUM_LENGTH, 
 NUMERIC_PRECISION, 
 NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY Tabela, Coluna

ou somente as tabelas:

SELECT
 TABLE_NAME as Tabela
FROM INFORMATION_SCHEMA.TABLES
ORDER BY Tabela

Pessoal até o próximo post!