asked on

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

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sujith

For Oracle, use this query.

select text from user_source where name = upper('your procedure name') order by type, line;

Sujith

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;

Howard Bash

ASKER

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

Howard Bash

ASKER

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

chapmandew

if you're using 2000, do this:

select text from syscomments
where object_name(object_id) = 'tablename'

Howard Bash

ASKER

By table name, do you mean "Stored Procedure" name?

SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial