0

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.

Kuntal Ghosh
  • 3,548
  • 2
  • 17
  • 21
  • *"Now I want to find out what operation(s) (insert/update/delete) those tables are doing inside every stored procedures."* Do you mean what DML operations are being performed against what tables in your Stored Procedures? Tables don't perform DML operations against Procedures; the tables are the ones affected by DML operations. – Thom A Mar 24 '21 at 11:59
  • This, however, is far too broad at best. To *really* achieve this you'd need the definition of your procedures and a tool that can parse SQL and identify keywords and the processes the batch is doing. This is *far* from a trivial task. This is, truthfully, where good documentation comes into play. – Thom A Mar 24 '21 at 12:01
  • DML operation like insert, update, delete. If we can find out for a single table, like Customer, what type of operations (insert/update/delete) getting performed on it inside Stored procedure, that is also fine for me. – Kuntal Ghosh Mar 24 '21 at 12:07
  • Start by finding the dependencies - which is a common topic and has many discussions and solutions posted. Beyond that, there are no easy methods to determine what operations a stored procedure takes on a given table. Don't forget views and triggers can also "contribute" to the information you seek. This is a situation where good documentation, code writing consistency, and good naming conventions can help. Likely none of those were a focus of the development team unfortunately. Lessons to be learned. – SMor Mar 24 '21 at 12:13
  • Exactly what and how do you want to see information about your procedures? Explain your manual process. Are you unable to find what you need in the system catalogue views? – Stu Mar 24 '21 at 13:10

1 Answers1

1

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.

Stu
  • 30,392
  • 6
  • 14
  • 33