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’

Add Row to Dataset Table /DataTable

DataSet dsFields = new DataSet();

string strQry = @” SELECT * FROM XYZ”;

// Settings.ConnectionString is where the Connection String is stored.

dsFields = OracleHelper.ExecuteDataset(Settings.ConnectionString, CommandType.Text, strQry);

DataRow newRow = dsFields.Tables[0].NewRow();

// Here I am adding only one column for presentation purpose.

// You can add columns as per your requirments.

newRow [“ColumnName”] = “ColumnName”;

newRow [“ColumnValue”] = “ColumnValue”;

// Adding row to Dataset-Table

dsFields.Tables[0].Rows.Add(newRow);

We can do similar way for DataTable also just replace dsFields.Tables[0] with your DataTable Name