Ao trabalhar com banco de dados, pode ser necessário em algum momento obter algumas informações em relação as nossas tabelas.
Algumas operações comuns são listar a quantidade de registros em cada tabela, conferir o tamanho que elas ocupam em bytes, ver a data de última atualização, conferir os índices e listar a estrutura de nossas tabelas.
Neste artigo você vai ver como fazer estas operações em banco de dados SQL Server e MySQL.
Vamos começar pelo SQL Server. Dentro do seu banco de dados, execute a seguinte consulta para listar as tabelas e a quantidade de registros de cada uma.
select
schema_name(schema_id) as 'owner', tabelas.name as 'tabela',
sum(partitions.rows) as 'linhas'
from sys.tables as tabelas
join sys.partitions as partitions on tabelas.object_id = partitions.object_id and partitions.index_id in (0,1)
group by schema_name(schema_id), tabelas.name
Para obter as tabelas e o espaço em MBytes que elas ocupam, execute a seguinte consulta:
SELECT
s.Name AS owner, t.Name AS tabela,
p.rows AS total_registros,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS MB_usados,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS MB_nao_utilizados,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS MB_total
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY s.Name, t.Name
Para ver quando as tabelas foram atualizadas pela última vez:
SELECT B.NAME AS 'TABLENAME', MAX(STATS_DATE (ID,INDID)) AS ultima_atualizacao,
CONVERT(VARCHAR, MAX(STATS_DATE (ID,INDID)), 103) as data
FROM SYS.SYSINDEXES AS A
INNER JOIN SYS.OBJECTS AS B ON A.ID = B.OBJECT_ID
WHERE B.TYPE = 'U' AND STATS_DATE (ID,INDID) IS NOT NULL
GROUP BY B.NAME
E se você quer listar todos os índices de seu banco de dados:
select i.[name] as index_name,
substring(column_names, 1, len(column_names)-1) as [colunas],
case when i.[type] = 1 then 'Clustered index'
when i.[type] = 2 then 'Nonclustered unique index'
when i.[type] = 3 then 'XML index'
when i.[type] = 4 then 'Spatial index'
when i.[type] = 5 then 'Clustered columnstore index'
when i.[type] = 6 then 'Nonclustered columnstore index'
when i.[type] = 7 then 'Nonclustered hash index'
end as tipo_indice,
case when i.is_unique = 1 then 'Unique'
else 'Not unique' end as [unique],
schema_name(t.schema_id) + '.' + t.[name] as tabela_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [tipo_objeto]
from sys.objects t
inner join sys.indexes i
on t.object_id = i.object_id
cross apply (select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = t.object_id
and ic.index_id = i.index_id
order by key_ordinal
for xml path ('') ) D (column_names)
where t.is_ms_shipped <> 1
and index_id > 0
order by i.[name]
Para criar uma lista com todas as tabelas e colunas de seu banco de dados:
select schema_name(tab.schema_id) as schema_name,
tab.name as tabela, col.column_id, col.name as coluna, t.name as data_type,
col.max_length, col.precision
from sys.tables as tab
inner join sys.columns as col
on tab.object_id = col.object_id
left join sys.types as t
on col.user_type_id = t.user_type_id
order by schema_name, table_name, column_id;
Vamos ver as mesmas operações só que desta vez utilizando o MySQL.
Para obter o tamanho da tabela em bytes e a quantide de registros, você pode utilizar a seguinte consulta:
SELECT
table_name as tabela,
SUM(data_length + index_length) AS 'DBSize',
SUM(TABLE_ROWS) AS DBRows,
SUM(AUTO_INCREMENT) AS DBAutoIncCount
FROM information_schema.tables
where TABLE_SCHEMA = 'nome_do_banco_de_dados'
GROUP BY table_name;
Para listar as tabelas e a data de última atualização a consulta é a seguinte:
SELECT table_name as tabela, update_time as ultima_atualizacao
FROM information_schema.tables WHERE TABLE_SCHEMA = 'nome_do_banco_de_dados'
Para listar todos os índices de seu banco de dados:
select index_schema, index_name, group_concat(column_name order by seq_in_index) as index_columns,
index_type,
case non_unique
when 1 then 'Not Unique'
else 'Unique'
end as is_unique,
table_name
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys')
and index_schema = 'nome_do_banco_de_dados'
group by index_schema, index_name, index_type, non_unique, table_name
order by index_schema, index_name;
Para listas as colunas do seu banco de dados:
select tab.table_name as tabela, col.ordinal_position as column_id,
col.column_name as coluna, col.data_type as data_type,
case when col.numeric_precision is not null
then col.numeric_precision
else col.character_maximum_length end as max_length,
case when col.datetime_precision is not null
then col.datetime_precision
when col.numeric_scale is not null
then col.numeric_scale
else 0 end as 'precision'
from information_schema.tables as tab
inner join information_schema.columns as col
on col.table_schema = tab.table_schema
and col.table_name = tab.table_name
where tab.table_type = 'BASE TABLE'
and tab.table_schema not in ('information_schema','mysql', 'performance_schema','sys')
and tab.table_schema = 'nome_do_banco_de_dados'
order by tab.table_name, col.ordinal_position;
Quer conferir mais dicas sobre banco de dados? Clique aqui e veja mais dicas!
Conteúdo sobre banco de dados sem complicação!
Veja outros conteúdos que podem ser de seu interesse
Cursos e outros materiais gratuitos para estudar programação
29/11/2020
Confira vários cursos gratuitos e blogs para aprender programação de graça
Consultar uma API de CEP usando SQL Server
16/02/2020
Veja como consumir uma API de CEP com o SQL Server
Serviços
Portfólio
Aplicativos
Videos