How to detect which stored procedures UPDATE or INSERT into a certain TABLE?

Question

I have many SQL files that represent stored procedures, these stored procedures are encrypted in the Database.

Is there any tool that allows me to detect which of these stored procedures UPDATE or INSERT into a certain TABLE?

Maybe a NOTEPAD++ regular expression could help me to achieve this?

Answer 1

To start, there is no way to derive information from a stored procedure definition or its metadata that will tell you, without any doubt, that this procedure performs an insert or an update on a specific table. SQL Server just doesn't maintain that type of info. RegEx might get you close, but you're relying on fallible string patterns.

Now, in your specific case, you have the added complication of encryption. There are tools out there that can decrypt stored procedure definitions, and you can search for them (there is an ethical reason why I won't hand those to you here).

In the meantime, since you have the unencrypted versions in text files, I suggest you use those to create the procedures, temporarily, in a dummy database, without encryption (you'll have to do some kind of search and replace to remove this option from the procedure declaration). Then you can use the built-in metadata (such as OBJECT_DEFINITION() or sys.sql_modules) to parse the text just like you would with the text files. Once the procedures are created (again, unencrypted), you can do something like this to find stored procedures which use the UPDATE keyword prior to mentioning your table name:

SELECT s.name, p.name, m.definition
  FROM sys.schemas AS s
  INNER JOIN sys.procedures AS p
  ON s.[schema_id] = p.[schema_id]
  INNER JOIN sys.sql_modules AS m
  ON p.[object_id] = m.[object_id]
  WHERE UPPER(m.definition) LIKE N'%UPDATE%TABLENAME%';

And change that up (or add an OR) to handle INSERT.

Now, this can lead to a lot of false positives:

• updates one table then selects from your table of interest
• has an update in a comment
• tablename is actually contained in other, longer object names (one of the reasons I prefer Customers over Customer, for example)

However, it should narrow down the list so that the manual work you have to do, in order to be confident you've identified the right set of procedures, is minimized. No automated string parsing will get you there 100% for many of the same "false positive" reasons. And don't forget that you may have procedures that use dynamic SQL and build the table name or the entire command from input variables - no string parsing is going to find those, either.

Answer 2

This worked for me:

select distinct o.name, o.type_desc, dep.is_updated
FROM sys.sql_modules   m 
INNER JOIN sys.objects o ON m.object_id=o.object_id
INNER JOIN sys.sql_dependencies dep ON m.object_id = dep.object_id
INNER JOIN sys.columns col ON dep.referenced_major_id = col.object_id
INNER JOIN sys.tables tab ON tab.object_id = col.object_id
WHERE tab.name = 'BasketOrderForm'
and is_updated = 1
ORDER BY O.name

Here is the Result:

Name                                    type_desc               is_updated
procBasketHelperMergeBasketOrderForms   SQL_STORED_PROCEDURE    1   
procListImport_Baskets                  SQL_STORED_PROCEDURE    1   
procListTransferInsertBasketOrderForm   SQL_STORED_PROCEDURE    1   
procTS360SubmitBasket                   SQL_STORED_PROCEDURE    1   
procTS360TransferBasketToUsers          SQL_STORED_PROCEDURE    1   
tdBasketLineItems                       SQL_TRIGGER             1   
tuBasketOrderForm                       SQL_TRIGGER             1