How to see Stored Procedure Code in Oracle & Sql Server

Suppose we are writing some new query/script/SP and we want to see the code of preexisting SP in query window itself without going Schema Browser or Directly going to that object

We can able to do this in both Oracle and Sql Server.

— Oracle

select * from USER_SOURCE where Name =’PRC_SEND_EMAIL’ — Provided you are given permission to access USER_SOURCE
there is also one more way

— DBA specific Schema specific need to have permission to see this.

select * from DBA_SOURCE where Name =’PRC_SEND_EMAIL’

— You can see signature of the procedure in Current Schema

Desc ‘PRC_SEND_EMAIL’

In Sql server we can do the same with the help of

sp_helptext ‘dbo.sp_helptext’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s