Parsing stored procedures

Question

Is there a way to scan a SQL Server to find all the stored procedures, read in their bodies, and determine a list of tables they use? My purpose is to perform an analysis on a large legacy database. (SQL Server 2008R2, preferably C#, but language is largely immaterial.)

Answered here perhaps? http://stackoverflow.com/questions/1916489/how-to-write-a-query-for-sql-server-2008-that-returns-the-dependencies-of-an-obj — jenson-button-event, Dec 07 '11 at 17:42
Some hints in the comments here: http://forums.asp.net/t/1225902.aspx We do something similar in our database although we have modified it to use some proprietary code so I can't post it. Our version lets us search all database on the server or only the ones we send in usinga variable. . — HLGEM, Dec 07 '11 at 21:20

score 3 · Accepted Answer · edited Jul 29 '20 at 18:17

3

Leverage SQL Server's sp_depends system stored procedure.

Get a list of all your stored procedures.

select  specific_name   from information_schema.routines
where routine_type='PROCEDURE' and specific_name not like 'sp_%'

Use this list along with sp_depends, and insert its results into a table.

declare @t table([name] varchar(50),[type] varchar(50),
updated varchar(10),selected varchar(10),[column] varchar(50))

insert into @t exec sp_depends 'MyProc1';
insert into @t exec sp_depends 'MyProc2';
insert into @t exec sp_depends 'MyProc3';

select [name] from @t group by [name];

More on sp_depends

edited Jul 29 '20 at 18:17

Community

1
1

answered Dec 07 '11 at 17:42

p.campbell

98,673
67
256
322

This doesn't work 100% for cross-database dependencies though. – JNK Dec 07 '11 at 18:17
@JNK : I missed the part where the OP wanted cross database functionality. – p.campbell Dec 07 '11 at 21:07
He didn't mention it, but I wanted to point out that `sp_depends` will not accurately report dependencies across databases. – JNK Dec 07 '11 at 21:12

score 0 · Answer 2 · answered Dec 07 '11 at 17:40

0

Not sure if this helps but you can right-click on any database in the Object Explorer of SQL Management Studio and use Tasks --> Generate Scripts to generate a large script containing the bodies of all stored procs (or any objects for that matter). At that point it's just a text file you can parse for whatever you want.

answered Dec 07 '11 at 17:40

Terry

14,099
9
56
84

I got the impression that OP wanted an automated, or semi-automated, way to do this across large databases, and this sounds a little cumbersome... – MJB Dec 08 '11 at 01:28
Her first sentence implies multiple databases, however she then goes on to mention a single large database - "My purpose is to perform an analysis on a large legacy database." Since I know for a fact this method works for a single database without being very cumbersome I thought I would suggest it. – Terry Dec 08 '11 at 01:44

score 0 · Answer 3 · answered Dec 07 '11 at 17:41

0

I think this may work:

SELECT 
    routine_name,
    routine_definition
FROM   information_schema.routines
WHERE  routine_definition in (SELECT '%' +name+ '%' FROM   sys.tables)
       AND routine_type IN ( 'function', 'pocedure' )

answered Dec 07 '11 at 17:41

reekeecast

66
4

score 0 · Answer 4 · answered Dec 07 '11 at 17:43

Yes for the first two points for sure:

SELECT
   pr.Name,
   mod.Definition
FROM sys.procedures pr
INNER JOIN sys.sql_modules mod ON pr.object_id = mod.object_id

Once you have the stored proc body (in sql_modules, column definition) you can parse it - but that'll be a bit messy at best - not sure if there's a more convenient way to do this...

Parsing stored procedures

4 Answers4