Search code examples
stored-proceduresadvantage-database-serverdata-dictionary

Hot to get stored procedure SQL text?

Is there any way to retrieve (and update) the actual stored procedure SQL text from a data dictionary?

A sample use case for this would be to write a replacement tool for the Advantage Data Architect. However at the moment I really need this to workaround a bug in ADS 9.1.

I imagine there must be something like:

EXECUTE PROCEDURE sp_GetStoredProcedureProperty('PROCNAME', 'SQLTEXT');

I found a system procedure called sp_ModifyProcedureProperty that can be used to modify some parts of a SP:

http://devzone.advantagedatabase.com/dz/WebHelp/Advantage10.1/index.html?master_sp_modifyprocedureproperty.htm

Solution

  • There is a system table in the dictionary called system.storedprocedures which has a field named SQL_Script that does what I need.

    Example:

    SELECT SQL_Script FROM system.storedprocedures WHERE Name = 'PROCNAME';