Get Stored proc SQL from StoredProcName

[GeppoDarkson]

Hi,
someone knows how to get the SQL Statements of a Stored procedure knowing the name?

I simply want to put the StoredProcedure source into a memo and let the user show it.

Thanks,
Geppo Darkson.

 

[Griffyn]

I would suggest this is not possible. Seeing as the SQL is generated via VBScript or somesuch, the SQL would not be available until just prior to execution within the Stored Procedure.

If it were possible, I would think you'd need to use routines from the libraries on the SQL Server.

 

[GeppoDarkson]

The stored procedure runs on a Oracle database, so i've take the SQL from the system tables:

select * from USER_SOURCE where name = 'THEPROCEDURENAME'

But it sounds me strange that there is no standard methods to obtain the SQL of a stored proc via ADO or some other Delphi component.

I can't belive, Delphi is too good.

 

[donvittorio]

The stored procedure is obviously part of the database, and hence has nothing to do with Delphi.
However, within your Delphi code you can use ADO (or any other database connectivity) to run a query to get the source code of the stored procedure from the system tables, in the same way as you would run any other query. In your last post you gave the query that you need to get the source code from the USER_SOURCE table, so I'm not sure what you need help with?

 

[GeppoDarkson]

I want to make a tool that works with many (all) databases, so I expect to find some ODBC or ADO function to obtain the stored proc statement.

After all it should be not too different as asking

AdoConnection1.GetProcedureNames, or AdoConnection1.GetTableNames,

both should looking at system tables, so with a little more effort by the vendor...