procedureddlteradatasystem-tables

How to extract Stored Procedure body in Teradata

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.

Solution

  • 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.