Stored Procedures and the Data Dictionary

Database

Stored Procedures and the Data Dictionary

Does anyone know if there is a data dictionary view or table that contains the source code for stored procedures? The RequestText column in DBC.tvm seems to be a pointer to another location, but I can't find it anywhere in the documentation. SQL Assistant is able to display the formatted text, so it has to exist somewhere, no?

Many Thanks!
4 REPLIES

Re: Stored Procedures and the Data Dictionary

Does anyone have a suggestion on how to pursue an answer to this? I need to perform comprehensive searches across a large number of stored procedures and macros, and doing so one at a time through TD SQL Assistant is error-prone and extremely tedious. SQL Assistant obviously can display the source code, including however it was formatted at compile time, so there must be a way to either search using SQL, or at a minimum dump all of the source code out to a file and use grep/sed/awk.

Re: Stored Procedures and the Data Dictionary

Well, it isn't a perfect solution, but I generated SHOW PROCEDURE statements for all of the procs, then exported them to a text file with BTEQ. This lets me find out (quickly) what stored procedures are accessing or updating a table. It has limitations, like it may be hard to tell if a particular line of code has been commented with /* */ but it is better than nothing.
Enthusiast

Re: Stored Procedures and the Data Dictionary

DBC.TableText contains the full text for tables, views and macros. I have used it for these, check it out for procedures.
Junior Contributor

Re: Stored Procedures and the Data Dictionary

The source code of a SP is not stored anywhere within the dbc tables.

If you explain a SHOW PROCEDURE you'll see it's stored as a special row within the SP "table".
Or you might get a 5535 error: No SPL source text available.

The only way to find out about dependencies is installing/using the Teradata MDS (Metadata Services).

Dieter