6

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.

Turgs
  • 1,729
  • 1
  • 20
  • 49
oscilatingcretin
  • 10,457
  • 39
  • 119
  • 206

2 Answers2

9

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.

Turgs
  • 1,729
  • 1
  • 20
  • 49
  • 2
    I ended up writing a .NET utility that does what you suggested and it works very well. One caveat that others may benefit from is that you must read `show procedure mydb.mysproc` into a TdDatareader and then do `while (reader.Read())` since each line in the sproc is a separate row. Simply returning a scalar will only return the first line of the sproc. Thanks for the suggestion. – oscilatingcretin Jul 23 '12 at 12:53
0

The SHOW PROCEDURE trick may not be always applicable now for Teradata 14.1 until Teradata 15 as tested; not sure though for the most recent release. This is because object names (i.e., Tables, Views, Stored Procedure, etc.) can now have a character length of more than 30 characters. If the object name is more than that, the DBC.Tables view will not be able to retrieve the record for the TABLENAME filtered using an equality condition. A "LIKE" keyword qualifier may need to be used instead. DBC.Tables has a SUBSTRING function in the view to still limit the name to 30. You can still get the full object/table name through DBC.TVM; but only if you have a SELECT access privilege which is usually not GRANTed other than the DBA.

Best regards, Win

Sirwin Naturre
  • 1
  • 1
    There was a recommendation to use the new Unicode-based set of system views since TD12, `dbc.TablesV` instead of `dbc.Tables`. – dnoeth Mar 30 '16 at 09:04