CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-functionsystem-stored-procedures
Kuntal Ghosh
Kuntal Ghosh

Reputation: 3698

What DML operations a table is performing inside Stored Procedure or SQL Functions

I am using SQL Server 2014. I have some user defined tables like Customer, PurchaseOrder, User, and so on. I am using those tables inside many stored procedures. In some cases, those stored procedures are almost 1000/1500 lines long.

Now I want to find out what operation(s) (insert/update/delete) those tables are doing inside every stored procedures.

I am doing it manually. But it is hell lot of effort. Besides, in manual effort, I might miss anything.

Can we write a SQL query by which without opening a stored procedure I can know what operation (insert/update/delete) a certain table is performing inside it.

Thanks in advance.

Upvotes: 0

Views: 843

Answers (1)

Stu
Stu

Reputation: 32579

Based on your requirements you may find the following useful. You can search the complete text of all procedures / functions / triggers / views etc and look for matching key words.

select Schema_Name(o.schema_id)[schema], o.[name], o.type_desc
from sys.sql_modules m
join sys.objects o on o.object_id=m.object_id
where 
    m.definition like '%insert%customers%' or
    m.definition like '%update%customers%' or
    m.definition like '%delete%customers%' 
order by type_desc, name

This can help you narrow down and identify potential objects. This in itself is not precise since it may find a procedure where you update orders and then use customers in a from or join subsequently.

If you have conventions you can rely on such as a delete will always be delete from customers and not delete customers or delete from c from... then you can of course improve the matching to increase the relevance of what you find.

A tool such as Redgate's SQLPrompt is invaluable here as you can script out all your procedure names prefixed with exec , paste it into SSMS and immediately preview the entire procedure code of each in a pop-up window.

Upvotes: 1

Related Questions