all proc definitions

Database
Enthusiast

all proc definitions

Hi all,

I want to get all procedure definitiond which reside in my teradata repository.

I tried following sql :

select requesttext from dbc.tables where tablekind='P'

But It gives definition in encrypted form.

So any one having any idea abt this.
2 REPLIES
Enthusiast

Re: all proc definitions

You will have to use SHOW PROCEDURE ; to get stored procedure definition. This is because SPs are internally stored by TD as a kind of system tables. (That's why you can't create an SP on a DB with Zero Perm).

A common work around, which you probably would know is to do a.

SELECT 'SHOW PROCEDURE ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';'
FROM DBC.TABLES
WHERE TABLEKIND = 'P' AND DATABASENAME = 'mySPdb';

then copy paste the results into a bteq and run it .....
rho
New Member

Re: all proc definitions

According to this topic in newer versions of Teradata it is better to use DBC.TablesV instead of DBC.Tables — thus the TableName column (and hence the stored procedure names) will not be limited to only 30 characters (but rather to 128) and the use of the TRIM function will not be required.