CODEWITHSUNDEEP
sql-server
PoolNoodleWeasel
PoolNoodleWeasel

Reputation: 3

Check if stored procedure modifies table data in SQL Server

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?

Upvotes: 0

Views: 1122

Answers (2)

Charlieface
Charlieface

Reputation: 72491

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

Upvotes: 1

Dordi
Dordi

Reputation: 778

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%'

Upvotes: 0

Related Questions