How to get a code of stored procedure ?

How to get a code of stored procedure ? I'm using System.Data.OleDb to
connect to server. OleDbSchemaGuid.Procedure_Parameters returns only
parameters but not a whole code.

Sahil
Wed Jan 05 14:47:37 CST 2005

This is specific to the datasource you are using.

In Sql Server there's a stored proc called sp_helptext 'sprocname' that will
spew out the stored procedure code.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

"Gregory" <glb_b@o2.pl> wrote in message
news:f94e46a7.0501051221.4dc9f844@posting.google.com...
> How to get a code of stored procedure ? I'm using System.Data.OleDb to
> connect to server. OleDbSchemaGuid.Procedure_Parameters returns only
> parameters but not a whole code.

Eric
Wed Jan 05 14:53:44 CST 2005

Well, without specifying what DB you are using its kind of tough to say.
I will assume SQL Server, but all major DBMS'es should have a way
to get at this.

In SQL Server There are a number of ways you can do this.

1) Select the appropariate rows from syscomments ...look up they system
tables in SQL Server Books on Line for more info. Probably a join
between sysobjects and syscomments.

2) Use a sql system stored procedure
sp_helptext MyStoredProcedureName

3) Use the INFORMATION_SCHEMA views that SQL Provides. These views are
part of the SQL-92 standard so are probably the most generic way to go
about it.
SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.Routines
WHERE ROUTINE_Name = 'TheStoredProcIWantToFind'

Hope that helps,
-eric


Gregory wrote:
> How to get a code of stored procedure ? I'm using System.Data.OleDb to
> connect to server. OleDbSchemaGuid.Procedure_Parameters returns only
> parameters but not a whole code.

Fredrik
Wed Jan 05 15:25:29 CST 2005


"Eric Barr" <REMOVE_First_22_chars_hanz_zarcovic@hotmail.com> wrote in
message news:OvWytk28EHA.1408@TK2MSFTNGP10.phx.gbl...
> Well, without specifying what DB you are using its kind of tough to say.
> I will assume SQL Server, but all major DBMS'es should have a way
> to get at this.
>
> In SQL Server There are a number of ways you can do this.
>
> 1) Select the appropariate rows from syscomments ...look up they system
> tables in SQL Server Books on Line for more info. Probably a join
> between sysobjects and syscomments.
>
> 2) Use a sql system stored procedure
> sp_helptext MyStoredProcedureName
>
> 3) Use the INFORMATION_SCHEMA views that SQL Provides. These views are
> part of the SQL-92 standard so are probably the most generic way to go
> about it.
> SELECT ROUTINE_DEFINITION
> FROM INFORMATION_SCHEMA.Routines
> WHERE ROUTINE_Name = 'TheStoredProcIWantToFind'
>
> Hope that helps,
> -eric
>

Of course, you can't get the source code if the stored procure was created
with the "WITH ENCRYPTION" clause. At least not if you are logged in as an
average joe. I'm not sure whether the administrator will be able to see it.
Anyone?

/ Fredrik

Sahil
Wed Jan 05 16:04:53 CST 2005

Yes the WITH ENCRYPTION stored procs can be deciphered.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

"Fredrik Wahlgren" <fredrik.p.wahlgren@mailbox.swipnet.se> wrote in message
news:eGkrK228EHA.3260@TK2MSFTNGP14.phx.gbl...
>
> "Eric Barr" <REMOVE_First_22_chars_hanz_zarcovic@hotmail.com> wrote in
> message news:OvWytk28EHA.1408@TK2MSFTNGP10.phx.gbl...
> > Well, without specifying what DB you are using its kind of tough to say.
> > I will assume SQL Server, but all major DBMS'es should have a way
> > to get at this.
> >
> > In SQL Server There are a number of ways you can do this.
> >
> > 1) Select the appropariate rows from syscomments ...look up they system
> > tables in SQL Server Books on Line for more info. Probably a join
> > between sysobjects and syscomments.
> >
> > 2) Use a sql system stored procedure
> > sp_helptext MyStoredProcedureName
> >
> > 3) Use the INFORMATION_SCHEMA views that SQL Provides. These views are
> > part of the SQL-92 standard so are probably the most generic way to go
> > about it.
> > SELECT ROUTINE_DEFINITION
> > FROM INFORMATION_SCHEMA.Routines
> > WHERE ROUTINE_Name = 'TheStoredProcIWantToFind'
> >
> > Hope that helps,
> > -eric
> >
>
> Of course, you can't get the source code if the stored procure was created
> with the "WITH ENCRYPTION" clause. At least not if you are logged in as an
> average joe. I'm not sure whether the administrator will be able to see
it.
> Anyone?
>
> / Fredrik
>
>

Fredrik
Wed Jan 05 16:19:33 CST 2005


"Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message
news:%23iUqVK38EHA.3596@TK2MSFTNGP12.phx.gbl...
> Yes the WITH ENCRYPTION stored procs can be deciphered.
>

Interesting. I assume this requires that you are logged in as the one who
created the sp or as administrator. What happens if you export the database
to some other instance of SQL Server? Can the administrator of that database
retrive the code as well?

/ Fredrik

Sahil
Wed Jan 05 17:17:10 CST 2005

> Can the administrator of that database
> retrive the code as well?

Yup :-)

Look @ this --
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


"Fredrik Wahlgren" <fredrik.p.wahlgren@mailbox.swipnet.se> wrote in message
news:eOQ$XU38EHA.3336@TK2MSFTNGP11.phx.gbl...
>
> "Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message
> news:%23iUqVK38EHA.3596@TK2MSFTNGP12.phx.gbl...
> > Yes the WITH ENCRYPTION stored procs can be deciphered.
> >
>
> Interesting. I assume this requires that you are logged in as the one who
> created the sp or as administrator. What happens if you export the
database
> to some other instance of SQL Server? Can the administrator of that
database
> retrive the code as well?
>
> / Fredrik
>
>

Fredrik
Wed Jan 05 17:35:22 CST 2005


"Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message
news:evq2uy38EHA.3504@TK2MSFTNGP12.phx.gbl...
> > Can the administrator of that database
> > retrive the code as well?
>
> Yup :-)
>
> Look @ this --
>
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5
>
> - Sahil Malik
> http://dotnetjunkies.com/weblog/sahilmalik
>
>

Yuck! I thought the idea of using WE was that it would help to protect
intellectual property.

/Fredrik