Atenção
Migramos este artigo para o novo site:
http://www.sqlfromhell.com/numero-de-linhas-de-todas-tabelas-de-um-banco-de-dados-do-azure/
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]
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:
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:
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
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!