How to get the create script of procedure or macro without knowledge about object type?

Database
ksh
N/A

How to get the create script of procedure or macro without knowledge about object type?

I need something like:

if (object is macro)
then
show macro object_name
else
show procedure object_name

But I had not find any way to do it. Could you please help?

Note: I can't create temporary tables, macros or procedures in the database.
3 REPLIES

Re: How to get the create script of procedure or macro without knowledge about object type?

SELECT TABLEKIND,
CASE
WHEN TABLEKIND = 'P' THEN
'SHOW PROCEDURE ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';'
WHEN TABLEKIND = 'M' THEN
'SHOW MACRO ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';' END
FROM DBC.TABLES WHERE TABLEKIND IN ('P','M')
ksh
N/A

Re: How to get the create script of procedure or macro without knowledge about object type?

Thanks for the quick answer.
But this query returns the statement I can use to get creation script while I need to get creation script ifself.
So is it possible to do it with one query?

Re: How to get the create script of procedure or macro without knowledge about object type?

(1) Using BTEQ take export the output of that SQL statement to a text file.
(2) Close the export
(3) Using the RUN command in BTEQ to run the file you just generated.

You can also insert commands into that text file to generate additional export commands to produce a flat file of the SHOW command outputs.