Retrieving the SQL underlying a Stored Procedure

Many moons ago,  I had written some code that used ADO/ADOX to retrieve the SQL underlying a stored procedure (for Oracle and MS SQL).  But,  I somehow lost that code and have been googling my head off looking for the method to repeat this function with.

Any and all help on this would be greatly appreciated.  I need this for Oracle and MS SQL and would prefer that the snippet was in VB.NET, but the language used for the answer here is not critical.

Thanks,
Howard
LVL 1
Howard BashSenior Software EngineerAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
in 2005 you can do this:


select object_definition(object_id)
from sys.procedures
where name = 'procedurename'
0
 
SujithData ArchitectCommented:
For Oracle, use this query.

select text from user_source where name = upper('your procedure name') order by type, line;
0
 
SujithData ArchitectCommented:
If the procedure belongs to another oracle user you may have to change the query to use all_source
or dba_source. something like

select text from all_source where owner = upper('user name') and name = 'TEST_PROC' order by type, line;
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Howard BashSenior Software EngineerAuthor Commented:
While both these queries work in their respective environments,  I was hoping to use something more generic like the ado/adox catalog object, etc...

Please advise.

Thanks,
Howard
0
 
Howard BashSenior Software EngineerAuthor Commented:
When I run this at home,  it fails with
Msg 195, Level 15, State 10, Line 2
'object_definition' is not a recognized function name.

When I type:
SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

I get back the following:
8.00.2273      SP4      Standard Edition

0
 
chapmandewCommented:
if you're using 2000, do this:


select text from syscomments
where object_name(object_id) = 'tablename'
0
 
Howard BashSenior Software EngineerAuthor Commented:
By table name,  do you mean "Stored Procedure" name?
0
 
chapmandewConnect With a Mentor Commented:
yeah, sorry about that....procedure name.  (have had a long day)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.