Dumping stored procedure source code into a text file

All code, nothing but code... here's a single SQL statement that will write the source code for all of your SQL Anywhere stored procedures into a single text file:

UNLOAD
SELECT STRING (
          '----------------------------------------------------------------\x0d\x0a-- ',
          SYSPROCEDURE.proc_name,
          '\x0d\x0a----------------------------------------------------------------\x0d\x0a',
          COALESCE ( SYSPROCEDURE.source, SYSPROCEDURE.proc_defn ),
          '\x0d\x0a\x0d\x0a' ) 
  FROM SYSPROCEDURE 
 WHERE USER_NAME ( SYSPROCEDURE.creator ) = 'DBA'
 ORDER BY SYSPROCEDURE.proc_name
TO 'c:/temp/procedures.sql'
DELIMITED BY '' 
ESCAPES OFF 
HEXADECIMAL OFF 
QUOTES OFF;

• The UNLOAD statement on lines 1 and 11 through 15 is wrapped around the SELECT on lines 2 through 10.

• The STRING call on lines 2 through 7 builds a character string containing the source code for one procedure, plus some line breaks and "-- header text".

• The COALESCE call on line 6 chooses which version of the source code to use. SYSPROCEDURE.source contains the code the way you wrote it if the preserve_source_format database option was 'On' when the procedure was last saved, and SYSPROCEDURE.proc_defn must be used if preserve_source_format was 'Off'.

  Note: The code in SYSPROCEDURE.proc_defn is a completely mangled version of slightly modified from the code you wrote. Here's how to ensure that your original code is always available in SYSPROCEDURE.source:
  

  
  SET OPTION PUBLIC.preserve_source_format = 'On';
  

• The TO clause on line 11 specifies where the file goes. This filespec is relative to where the database server is running, not where dbisql is running, so it's often safest to include the entire drive and path. SQL Anywhere interprets the forward slash "/" in a file specification string as a Windows backslash "\". If you want to code backslashes in a string literal then double them up because "\" is an escape character; in this case use 'c:\\temp\\procedures.txt' to be safe.

• The four clauses on lines 12 through 15 force the UNLOAD statement to obey the rule "what you have is what you get". In other words, the contents of the STRING() are written to the text file without any reformatting, no 'quotes', no \escape characters, no \0x hex numbers, no messin' around with my data! Not every one of these four clauses are necessary in every "what you have is what you get" context, but they never hurt... I'm not sure which ones are necessary here, and I don't give [an expletive deleted] care.

Here's a bit of the output from the Foxhound database:

----------------------------------------------------------------
-- f_forward_string_function_call
----------------------------------------------------------------
create FUNCTION f_forward_string_function_call (
   IN @p_remote_server_name   VARCHAR ( 128 ), 
   IN @p_string_function_call LONG VARCHAR )
   RETURNS LONG VARCHAR
   NOT DETERMINISTIC
BEGIN
... [other stuff]
END
... [lots and lots of other stuff]
----------------------------------------------------------------
-- rroad_verify_session_id
----------------------------------------------------------------
create PROCEDURE rroad_verify_session_id (
   IN  @p_session_id    VARCHAR ( 36 ),
   OUT @p_error_message LONG VARCHAR )
BEGIN
... [other stuff]
END

The UNLOAD SELECT code is easy to customize, to

• change the format of the "-- header text", or eliminate the headers altogether,

• include the owner name in the output if more than one procedure has the same name [censored] (polymorphism sucks big wampum, but that's not politically correct so I can't say that) [/censored],

• change the WHERE clause to SELECT different subsets of procedures,

• include the COMMENT ON procedure text if you have any,

• set APPEND ON instead of OFF, and/or

• write each procedure to a separate file.

Huh? You call that EASY?

Well, maybe that last one isn't quite as easy as the others, so here goes...

FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR
SELECT SYSPROCEDURE.proc_name AS @procedure_name,
       STRING (
          '----------------------------------------------------------------\x0d\x0a-- ',
          SYSPROCEDURE.proc_name,
          '\x0d\x0a----------------------------------------------------------------\x0d\x0a',
          COALESCE ( SYSPROCEDURE.source, SYSPROCEDURE.proc_defn ),
          '\x0d\x0a\x0d\x0a' ) AS @procedure_source_code,
       STRING ( 'c:/temp/procedures/', @procedure_name, '.sql' ) AS @output_filespec 
  FROM SYSPROCEDURE 
 WHERE USER_NAME ( SYSPROCEDURE.creator ) = 'DBA'
 ORDER BY SYSPROCEDURE.proc_name
FOR READ ONLY
DO
   MESSAGE STRING ( 'Writing ', @procedure_name ) TO CLIENT;
   UNLOAD SELECT @procedure_source_code 
      TO @output_filespec
      DELIMITED BY '' 
      ESCAPES OFF 
      HEXADECIMAL OFF 
      QUOTES OFF;
END FOR;
MESSAGE 'Done' TO CLIENT;

Writing f_forward_string_function_call
...
Writing rroad_verify_session_id
114 row(s) affected
Execution time: 0.23 seconds
Done
Execution time: 0.019 seconds

Actually, that was pretty easy... the FOR loop rocks! ...it beats the snot stuffing out of DECLARE CURSOR, DECLARE local variables, OPEN cursor, FETCH INTO all those variables, IF SQLCODE LEAVE or WHILE @@FETCH_STATUS or DECLARE EXCEPTION, CLOSE cursor, all that cursor loop carp you have to use in Transact SQL... not to mention worrying about CLOSE_ON_ENDTRANS (oh, you forgot about that one, didn't you? :)

PS: If you are wondering about @procedure_source_code's data type (and you should be), it's LONG VARCHAR according to the EXPRTYPE function (which also rocks! EXPRTYPE, and being able to use LONG VARCHAR for local variables, both of them rock!)

---