asked
Searching Stored Procedures
Hello, I realize that this is kind of a long-shot question. I have been tasked with compiling a list of the top reports based on the number of hits. I've done that, but the next step is to search through various stored procedures across multiple databases and identify the tables that are called out. I've started down the path of manually searching through each one. Before I get too far in to it, I was wondering if there is some kind of script or utility or anything that could retrieve the names of the tables other than looking through each SP. Thanks. Tom
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
See if you can tweak the following scripts to use them for your purpose. If I need to find all Stored procedures referencing a particular table, I would use the following script: SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON
sc.id =
so.id WHERE sc.TEXT LIKE '%tablename%' -- The following script list Stored Procedures and table names it reference in the current database. ;WITH stored_procedures AS ( SELECT o.name AS proc_name, oo.name AS table_name, ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row FROM sysdepends d INNER JOIN sysobjects o ON
o.id=
d.id INNER JOIN sysobjects oo ON
oo.id=d.depid WHERE o.xtype = 'P') SELECT proc_name, table_name FROM stored_procedures WHERE row = 1 ORDER BY proc_name,table_name
This should show you the script of the stored procedures. You could then search for anything that has "dbo." in it. SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES If you dont put "dbo." in front of your tables then you could get a list of all the tables in your database and then search for them. (If no one solves it within a week I will have a play and see what I can do)
The issue with your script is that it pulls back the entire stored procedure. Also, we stick "dbo" in front of quite a few objects. We also have inconsistent naming conventions. For example, some tables are dbo.tbl_???? and some are not. I suppose I could tweak a WHERE statement.
Red Gate does a [SQL Dependency Tracker][1] tool. OK, it's not a free solution, but you can get a free trial. And it would do what you want. [1]:
http://www.red-gate.com/products/sql-development/sql-dependency-tracker/
That's the one thing I would have pointed to as well. There's no easy way to do this through code that I'm aware of.
Try the below code. This should give you list of procedures name. Just replace "Table Name" with your table name. SELECT NAME , TYPE_DESC FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%Table Name%'
I am looking for the names of the tables that are called out in the SPs from the various databases.
If you were to rightclick on the table in SSMS you will be able to select View Dependencies and see the dependencies. You can also right click on the stored procedure and see the same. You can also see which objects depend upon the SP or table.
23 People are following this question.
