Analyzing SQL Server Dependencies – Read-only Stored Procedures

Bonus post for my “Analyzing SQL Server Dependencies” series answering the SO question Is it possible to get list of stored procs which only read data?

This was my first try (SQL Server 2008)

select o.name
from sys.objects o
where o.type = 'P' and o.object_id not in (
    select d.object_id from sys.sql_dependencies d where is_updated = 1
)
order by 1

As SO user gvee pointed out, the system view sys.sql_dependencies is deprecated in SQL Server 2012:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.sql_expression_dependencies instead.

So I needed to rewrite the query for SQL Server 2012 and higher:

select o.name,  OBJECT_SCHEMA_NAME(o.object_id), *
from sys.objects o
where o.type = 'P' and o.name not in ('sp_upgraddiagrams')
and not exists (
    select 1
    from  sys.dm_sql_referenced_entities(
        OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name, 'OBJECT')
    where is_updated = 1
)
order by 1

OP noted that the is_updated flag is not set for stored procedures that TRUNCATE tables.

I’d suggest the following work-around:

TRUNCATE TABLE MyTable
IF 0=0 DELETE MyTable

Didn’t test, should work 😉