November 2, 2004 at 5:24 pm
I'm looking for a SQL script to find all the stored procedures used to be transferred over to another database. The search needs to be recursive since a stored procedure within the first stored procedure may call yet another stored procedure. For example, I start with the stored procedure "s_GetMemberData.sql" which calls the stored procedure "s_GetMemberAddress.sql" and "s_GetMemberAge.sql", and then the "s_GetMemberAge.sql" stored procedure calls "s_GetMemberBirthdate.sql" (plus, if the "s_GetMemberBirthdate.sql" stored procedure used any other stored proc, then it would need to be listed also). I would need the script to list all of these stored procedures.
Please help...
November 3, 2004 at 3:38 am
Hi Greg,
Just do a search through the scripts section. Most of the things you want are actually already there.
I think this should suit your requirements:-
http://qa.sqlservercentral.com/scripts/contributions/434.asp
Have Fun
Best regards
Steve
We need men who can dream of things that never were.
November 4, 2004 at 7:32 am
Greg,
If I understand your question, you have a database with many sp's and you do not know which of them are active and which of them are older version that are no longer used or maybe an older version of another sp. If this is the problem, then I do not think that there will be an easy fix for you.
In my situation I have sp's called from VB applications directly. There are also sp's that are called from jobs, which may run with a frequency from every 1 min to 1 yearly quarter.
Plus, of course, some of these sp's call other sp's that are never called directly, but only by other sp's.
And then there are the sp's that are only called on an ad hoc basis, by the dba staff.
I use two main tools to look at each of the sp's to see how it fits into the main scheme of things:
-- find text within a stored procedure
select so.name from syscomments sc
join sysobjects so on so.id=sc.id
where sc.text like '%datafeedworkbase%'
group by so.name
order by so.name
--To find job step that calls a sp
select sj.name,sjs.step_name,sjs.command from msdb..sysjobs sj
join msdb..sysjobsteps sjs on sjs.job_id = sj.job_id
where sjs.command like '%csp_DataFeed%'
order by sj.name
Based on what I find with these 2 procedures, if I think the sp or the table, etc, is not in use anymore, I will then rename with an & in front and after a safe amount of time, I will then delete all objects beginning with &.
If you find a better solution, please share it with me.
John Campbell
November 4, 2004 at 11:12 am
thats kinda what I'm needing...basically I am wanting a script to go thru a list of stored procedures (we'll call this stored proc list A) to check for calls to other stored procedures (we'll call this stored proc list B). but the stored procedures from list B might call other stored procedures (which could then call other stored procedures). I need to find a listing of only the stored procedures that would be in this iteritive search. I'm not sure if this makes sense or not. If you have any ideas then let me know.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply