Comandos uteis para trabalhar com tabelas no SQL

Algumas dicas de como listar a quantidade de registros, data de atualização e índices de tabelas no SQL Server e no MySQL

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!

Veja outros conteúdos que podem ser de seu interesse