Stored Procedure Source SQL

[chris.nillissen]

Is there a way to get the source SQL of a stored procedure using SDAC?

I need to parse the actual SP SQL code rather than execute the SP.

[AndreyZ]

Hello,

You can get the source code of a stored procedure in two ways:
1) by executing a query to INFORMATION_SCHEMA:

Code: Select all

MSQuery.SQL.Text := 'select ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES Where ROUTINE_NAME=''stored_proc_name''';
MSQuery.Open;

2) by using the sp_helptext system stored procedure:

Code: Select all

MSQuery.SQL.Text := 'sp_helptext @objname = ''stored_proc_name''';
MSQuery.Open;