Geek asks and answers

Can I request the RequestText procedure in Teradata?

Teradata has a useful look called dbc.tablesthat you can use to query objects. I used this to successfully query RequestTextin views, but the procedures seem to have compiled somewhat differently.

When I look at the RequestTextprocedures (TableKind P), everything I get looks something like this:

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

Is this a reference to the actual RequestText, which is stored elsewhere? How do I get to it?

I want to actually query it using SQL, rather than dumping it into a text file.

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

+5
source share
2 answers

Stored procedures are compiled as an executable object. On Windows, they are compiled into a DLL. On Unix, they are combined into common objects. These objects are stored in their own database.

Here a similar question was asked on the Teradata forum .

View procedure source code

Since they are compiled, the source code will not appear somewhere like DBC.Tables, therefore, unfortunately, as you have already found, the following query regarding DBC.Tableswill not work.

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

Instead, the only way to get the stored procedure code is to do the following:

SHOW PROCEDURE mydb.procedurename;

How to find procedures containing specific

, VBScript VBA Teradata ODBC. , :

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

, VBA.

SHOW PROCEDURE <dbname>.<tablename>;

- InStr(), , .

, , , -, .

- DBQL, .

"" , SQL, .

, , LIKE.

+7

SHOW PROCEDURE Teradata 14.1 , Teradata 15 ; , . , (.. , , ..) 30 . , DBC.Tables TABLENAME, . "LIKE". DBC.Tables SUBSTRING , 30. / DBC.TVM; SELECT, GRANTed, DBA.

, Win

0

More articles:


All Articles