Can I query a procedure's RequestText in Teradata?

Question

Teradata has a useful view called dbc.tables that you can use to query objects. I've used this to query the RequestText in views with success, but procedures seem to be compiled a bit differently.

When I look at the RequestText of a procedure (TableKind P), all I get is something like this:

ALTER PROCEDURE '468137'xn.'546321987654321345646556213165468654654654'xn COMPILE /* mydb.procedurename */;

Is this a reference to the actual RequestText that's stored elsewhere? How do I get to it?

I want to actually query it with SQL, not dump it to a text file.

The reason why is because I need to run a LIKE statement against it to search for references to a specific table name. It would be inconvient to have to dump it to a text file and then search the text file manually.

Answer 1

Stored Procedures are compiled to be an executable object. On Windows, they're compiled into DLLs. On Unix, they're compiled into Shared Objects. Those objects are stored within the owning database.

Here's a similar question asked on the Teradata Forums.

Viewing the Original Procedure Code

Since they're compiled, the original code won't we visible in somewhere like DBC.Tables, so unfortunately as you've found, the following query against DBC.Tables won't work.

SELECT *
FROM DBC.Tables
WHERE TableKind = 'P'
AND RequestText LIKE '%abc%';

Instead, the only way to retrieve the Stored Procedure code is to execute the following:

SHOW PROCEDURE mydb.procedurename;

How to Find Stored Procedures That Contain a Specific String of Characters

What I would do is to use VBScript or VBA to connect to Teradata via ODBC. I would first run this to get a list of all Stored Procedures:

SELECT *
FROM DBC.Tables
WHERE TableKind = 'P';

Then, I would loop through each result and run the following, saving the result to a variable in VBA.

SHOW PROCEDURE <dbname>.<tablename>;

I would then use something like the InStr() function to find when the code contains a certain string of text.

Granted, this is a lot more involved that I think you were initially hoping for.

Alternate Option

An alternate option is to utilise tables in the DBQL database, if they're available to you.

This database contains some "logging" tables where you can see a history of all SQL statements executed on the system.

That way, you can run the type of query you initially were hoping for using a LIKE condition.