[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

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
0
Howard Bash
Asked:
Howard Bash
  • 3
  • 3
  • 2
2 Solutions
 
chapmandewCommented:
in 2005 you can do this:


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

select text from user_source where name = upper('your procedure name') order by type, line;
0
 
sujith80Commented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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
 
chapmandewCommented:
yeah, sorry about that....procedure name.  (have had a long day)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now