Extracting text for macros, views and stored procedures

Teradata Employee

Extracting text for macros, views and stored procedures

I am looking for a way to quickly retrieve all macro, view and stored procedure text. People on this thread have already recommended using the show procedure/view/marcro commands, but that would require calling everything sequentially.

I have checked and it appears that the dbc.tvm.CreateText and dbc.tabletext contains the current definitions for those objects. Is there any drawback to going straight to these tables over using the show command?

Tags (1)
Teradata Employee

Re: Extracting text for macros, views and stored procedures


Retrieving the code from dbc.tvm.CreateText, you will lose the formatting, comments and all the object names will be fully qualified. The overall won’t be really readable.

Using dbc.tvm.RequestText, you can retrieve the latest DDL statement that was executed against the object. Right now, in case of a view/macro/procedure, I can’t think about anything but a create or replace statement, so you should be able get the code, as entered by the user. I am quite sure there are some exceptions I am missing tough...

Both these fields limited in length to respectively 13k and 12.5k characters. Longer statements can be retrieved from dbc.tabletext.

I would strongly recommend using show, to be on the safe side, get a consistent result and readable code. You can easily script this in a couple of command lines, calling beteq, and parallelize the calls if you like.