Como identificar tabelas dentro de uma Procedure

Tempo de leitura: 2 minutos

Olá Pessoal,

Espero que estejam todos bem ?

A dica de hoje é bem rapidinha!!

Passando para compartilhar uma query que utilizo bastante no meu dia a dia.

Você já se deparou com a necessidade de identificar os objetos (tabelas) dentro de uma procedure?

Imaginem que você está fazendo um troubleshooting para tentar identificar a performance do seu código e percebe que a sua procedure tem milhares e milhares de linhas, olhando para diversas tabelas.

Seria bem trabalhoso validar no olho quais tabelas estão sendo utilizadas na procedure, não é mesmo?

Uma das primeiras coisas que um DBA deve verificar geralmente é como estão as amostragens das estatísticas, porém, para fazer isso precisamos saber primeiro quais são os objetos da nossa procedure.

Para esses casos, utilizo o comando abaixo :

SELECT DISTINCT 
	[object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name,
	o.type_desc
FROM sys.dm_sql_referenced_entities('dbo.rpt_TopPosts_FromTopLocations','OBJECT') d
     JOIN sys.objects o
	 ON d.referenced_id = o.[object_id]
WHERE o.[type] IN ('U','V');

SELECT DISTINCT

[object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name,

o.type_desc

FROM sys.dm_sql_referenced_entities('dbo.rpt_TopPosts_FromTopLocations','OBJECT') d

JOIN sys.objects o

ON d.referenced_id = o.[object_id]

WHERE o.[type] IN ('U','V');

Neste exemplo, eu queria saber quais eram os objetos presentes na procedure dbo.rpt_TopPosts_FromTopLocations. Passamos o nome da procedure como parâmetro na sys.dm_sql_referenced_entities e assim conseguimos extrair o resultado desejado.

Bem legal né??? ?

Eu usava uma query antes com o mesmo propósito, porém, o mestre Jedi Fabiano Amorim, me mostrou que a minha consulta estava trazendo alguns dados divergentes em algumas situações, com essa sacada da sys.dm_sql_referenced_entities a consulta passou a filtrar certinho os objetos. Resolvi então compartilhar com vocês pois sei da grande utilidade disso no nosso dia a dia. ?

Dica Bonus :

Com essa outra query, conseguimos facilmente identificar como estão as amostragens das estatísticas já construindo os comandos de UPDATE STATISTICS dos nossos objetos.

-- gera update statistics

select distinct
       st.[name],
       stp.rows,
       stp.rows_sampled,
       ' update statistics ' + '[' + ss.name + ']' + '.[' + object_name(st.object_id) + ']' + ' ' + '[' + st.name + ']'
       + ' with fullscan'
from sys.stats as st
    cross apply sys.dm_db_stats_properties(st.object_id, st.stats_id) as stp
    join sys.tables sta
        on st.[object_id] = sta.object_id
    join sys.schemas ss
        on ss.schema_id = sta.schema_id
where sta.name = 'Posts'
order by [rows] desc;

-- gera update statistics

select distinct

st.[name],

stp.rows,

stp.rows_sampled,

' update statistics ' + '[' + ss.name + ']' + '.[' + object_name(st.object_id) + ']' + ' ' + '[' + st.name + ']'

+ ' with fullscan'

from sys.stats as st

cross apply sys.dm_db_stats_properties(st.object_id, st.stats_id) as stp

join sys.tables sta

on st.[object_id] = sta.object_id

join sys.schemas ss

on ss.schema_id = sta.schema_id

where sta.name = 'Posts'

order by [rows] desc;

E ai, gostaram dessa dica?

Se gostou deixa um comentário!!!

Gustavo Larocca

Consultor SQL Server

amostragem DBA Estatísticas fullscan microsoft Objetos Procedures sql server sys.dm_sql_referencecd_entities Tabelas Update Statistics

4 comentários

taianycoelhodba disse:

22 de março de 2022 às 11:11

Dica excelente demais 😉 super útil no nosso dia a dia.

#quedicas

Responder
Luiz Vitor França Lima disse:

22 de março de 2022 às 22:17

Meu blogueiro favorito!!! Amo suas dicas seu lindo S2!

Abraço,
Luiz Vitor

Responder
Paulo Gonçalves dos Santos disse:

19 de abril de 2022 às 08:08

Parabéns. Dica muito útil..Já estou utilizando

Responder
Eduardo Roedel disse:

1 de maio de 2022 às 12:35

Perdi a conta de quantas vezes essa dica me ajudou a resolver as tretas no dia a dia… Obrigado por compartilhar dicas sensacionais como esta… <3

Responder

Deixe uma respostaCancelar resposta

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Como identificar tabelas dentro de uma Procedure