Check if stored procedure modifies table data in SQL Server-CodePudding

I am granting a user group permissions to execute all stored procedures in a database which contain the word "Report" or "PDF", on the condition that the execution of these stored procedures will not modify the data in the database.

Now, I am currently reading through each of these stored procedures one at a time and basically doing a code review on each of them with the intention of determining if they modify data, or if they simply retrieve data.

Is there a programmatic way to test for the modification of the database in a single-run procedure that only gets run when the programmers want it run?

CodePudding user response：

You can get this information from the sys.dm_sql_referenced_entities system function. is_updated will be 1 when any table is inserted, updated or deleted.

SELECT
  schema_name = s.name,
  p.name,
  is_updated = CAST(
      CASE WHEN EXISTS (SELECT 1
        FROM sys.dm_sql_referenced_entities(QUOTENAME(s.name)   '.'   QUOTENAME(p.name), 'OBJECT') r
        WHERE r.is_updated = 1)
      THEN 1 ELSE 0 END
    AS bit)
FROM sys.procedures p
JOIN sys.schemas s ON s.schema_id = p.schema_id
WHERE (p.name LIKE '%Report%' OR p.name LIKE '%PDF%')
  AND p.is_ms_shipped = 0;

db<>fiddle

CodePudding user response：

You can look for words like INSERT, UPDATE,DELETE... in the stored procedure code, here's an exmple of the query :

SELECT *
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%INSERT%'
    OR OBJECT_DEFINITION(OBJECT_ID) LIKE '%UPDATE%'