Find stored procedure names from database with only SELECT statement-CodePudding

Find stored procedure names from database with only SELECT statements (procedures with update, insert should not be returned in that list).

CodePudding user response：

Seems like an odd request, but you can leverage sys.sql_module

SELECT [schema_name] = OBJECT_SCHEMA_NAME([object_id]) 
      ,[object_name] = OBJECT_NAME([object_id]) 
      ,definition
FROM sys.sql_modules
Where charindex('UPDATE ',definition)
      charindex('DELETE ',definition)
      charindex('INSERT ',definition)  
     =0

CodePudding user response：

Trying to parse the procedure SQL yourself is a recipe for disaster. What happens if one of your procedures has any of the following?

SELECT * FROM [UpdateTable]

-- We don't want to update

The definitive way to do this is to use sys.dm_sql_referenced_entities.

select schema_name = s.name, p.name
from sys.procedures p
join sys.schemas s on s.schema_id = p.schema_id
where not exists (select 1
    from sys.dm_sql_referenced_entities(s.name   '.'   p.name, 'OBJECT') r
    where r.is_updated = 1
);

Note that this also takes into account any type of DML, such as INSERT MERGE or DELETE

db<>fiddle