Comandos uteis para trabalhar com tabelas no SQL
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]
Como ver a estrutura de uma tabela no SQL Server? 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;