How to extract Stored Procedure body in Teradata

procedure ddl teradata system-tables
MaterialGirl picture MaterialGirl · Jun 18, 2013 · Viewed 13.5k times · Source

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.

Answer

Rob Paller picture Rob Paller · Jun 18, 2013

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.

Related questions

What is the difference between a "function" and a "procedure"?

Generally speaking, we all hear about the functions or procedures in programming languages. However, I just found out that I use these terms almost interchangeably (which is probably very wrong). So, my question is: What is the difference in terms …

Read More
How to call a mysql stored procedure, with arguments, from command line?

How can I call a stored procedure from command line? I have a procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `insertEvent`(IN `dateTimeIN` DATETIME) NO SQL BEGIN SET @eventIDOut = NULL; IF EXISTS(SELECT * FROM `events` WHERE `eventDate` = dateTimeIN) THEN SELECT `eID` …

Read More
set serveroutput on in oracle procedure

I've created a simple procedure. In this procedure i want to output some data. However where ever i put set serveroutput on it says Error(26,5): PLS-00103: Encountered the symbol "SERVEROUTPUT" when expecting one of the following: . ( ) , * @ % & = - + < / > …

Read More