Relacionando as colunas de cada índice do SQL Server

Da mesma forma que é possível relacionar as colunas de cada tabela no SQL Server, podemos aplicar o mesmo conceito sobre os índices, tornando um recurso útil para identificar índices muito semelhantes (ou até mesmo, combinar com consultas que relacionam informações sobre fragmentação e utilização dos índices):

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

-- Detalhes do índice
, i.[type_desc], i.[is_primary_key], i.[is_unique], i.[is_unique_constraint]
, ISNULL(i.name, '') AS [index]
, ISNULL(SUBSTRING(c.[indexed], 0, LEN(c.[indexed])), '') AS [indexed]
, ISNULL(SUBSTRING(c.[included], 0, LEN(c.[included])), '') AS [included]

-- Filtro utilizado pelo índice
, ISNULL(i.filter_definition, '') AS [filtered]

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

-- Relação de colunas que formam o índice
CROSS APPLY (
	SELECT (
		SELECT c.name + ', '
		FROM sys.columns c
		INNER JOIN sys.index_columns ic
		ON c.[object_id] = ic.[object_id]
		AND c.[column_id] = ic.[column_id]
		WHERE t.[object_id] = c.[object_id]
		AND ic.[index_id] = i.[index_id]
		AND ic.[is_included_column] = 0
		ORDER BY [key_ordinal]
		FOR XML PATH('')
	) AS [indexed]
	,(
		SELECT c.name + ', '
		FROM sys.columns c
		INNER JOIN sys.index_columns ic
		ON c.[object_id] = ic.[object_id]
		AND c.[column_id] = ic.[column_id]
		WHERE t.[object_id] = c.[object_id]
		AND ic.[index_id] = i.[index_id]
		AND ic.[is_included_column] = 1
		ORDER BY [key_ordinal]
		FOR XML PATH('')
	) AS [included]
) AS c
ORDER BY [schema], [table]

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:

Número de registros de todas tabelas de um banco de dados

Boa noite pessoas!

Como de uma forma prática podemos identificar quantos registros possuem todas as tabelas de um banco de dados? Bom, ao executar uma consulta semelhante a que temos abaixo, podemos realizar este procedimento para uma tabela.

SELECT COUNT(*) FROM Tabela

E para identificar quantos registros possuem várias tabelas poderíamos pensar em elaborar um CURSOR (tradução livre: entidade demoníaca que induz o programador aos artifícios técnicos não documentados), que pode executar para cada uma das tabelas do banco de dados a consulta semelhante a que temos acima. Mas será que é a forma mais adequada?

Ok, como alternativa, eu proponho utilizar os índices HEAP (tradução livre: estrutura caótica de armazenamento de registros) e CLUSTERED (tradução livre: estrutura organizada para o armazenamento de registros) das tabelas por meio da consulta:

SELECT *
FROM sys.indexes
WHERE [type] IN (0,1)

E a partir destes índeces, identificar quantas linhas/registros que estão relacionadas a eles:

SELECT
  i.[object_id]
, p.[rows]
FROM sys.indexes i
INNER JOIN sys.partitions p
  ON p.[object_id] = i.[object_id]
  AND p.[index_id] = i.[index_id]
WHERE i.[type] IN (0,1)

Visto que podemos identificar a tabela relacionada a estes índices por meio da coluna object_id, concluímos as consulta desta forma:

SELECT
  s.[name]
, t.[name]
, p.[rows]
FROM sys.schemas s
INNER JOIN sys.tables t
  ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i
  ON i.[object_id] = t.[object_id]
  AND i.[type] IN (0,1)
INNER JOIN sys.partitions p
  ON p.[object_id] = t.[object_id]
  AND p.[index_id] = i.[index_id]
ORDER BY
  s.[name]
, t.[name]

Outra pergunta, posso utilizar só a sys.partitions relacionada a sys.tables para identificar a quantidades de registros da tabela? Até pode, mas os resultados serão de acordo com os vários tipos de índices que as tabelas e views podem ter, que algumas vezes estes índices não obedecem a mesma regra aplicada aos índices HEAP e CLUSTERED.

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. http://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!