Como identificar tabelas dentro de uma Procedure

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');

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;