How to get a code of stored procedure ?

  • Thread starter Thread starter Gregory
  • Start date Start date
G

Gregory

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.
 
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
 
Eric Barr said:
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
Click to expand...

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 Malik said:
Yes the WITH ENCRYPTION stored procs can be deciphered.
Click to expand...

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
 
Back
Top