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]

 

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!
BINS - Blog SQL

 

Veja outros conteúdos que podem ser de seu interesse