3

I'm trying to extract Stored procedure DDL by querying system tables.
If I run the following query

select * from dbc.tvm where TableKind = 'P'

both fields RequestText and CreateText contain NULL. Is there any way to query Stored Procedure body apart from using SHOW PROCEDURE?

Thank you.

MaterialGirl
  • 363
  • 2
  • 10
  • 22

1 Answers1

2

The DDL (SPL) for the Stored Procedures is not stored in the data dictionary tables. If you do not retain your DDL in a repository for version control you will need to script the SHOW PROCEDURE commands in a BTEQ script and export them to flat files. This BTEQ script can be generated dynamically if you are creative with your queries against the data dictionary.

Rob Paller
  • 7,736
  • 29
  • 26
  • Rob, thank you very much for your answer. Now the situation with Procedures is clear. It looks like Triggers are organized a bit differently as CreateText column of DBC.triggersTbl system table contains DDL. – MaterialGirl Jun 19 '13 at 11:31
  • @Rob but where is the definition saved actually? – Ali Apr 06 '15 at 12:15
  • It is stored in a special row of the "SP" table. Try performing an EXPLAIN on a SHOW PROCEDURE command. – Rob Paller Apr 06 '15 at 14:23