Table Query

UDA
N/A

Table Query

Hi, I am new to Teradata.
I am looking for a table that holds the text of a Stored Procedure.
In essence I am trying to create a query that will show me the dependant objects if I pass the query a table name or string that is contained in the text of each dependant object.
For example: If the tableName 'Marc' exists in 2 stored procedures I want to return the following:

SProc_Name
---------------------
sp_Marc
sp_Marc1

Is their a way to do this via SQL or a utility?

ps: I used to use a join between the Sysobjects and Syscomments table in SQLServer.

Thanks in advance.
4 REPLIES
N/A

Re: Table Query

I've been reading about the Teradata Metadata Services Utility.
It seems to have what I'm looking for.
Has anyone had much experience with this utility and do you think it could fulfil my requirement? I havn't received the TTU Utilities package yet so I havn't had a chance to look at it yet so any insight would be extremely useful.

Re: Table Query



select tableName from dbc.tables where tableKind = 'p' and requesttext like '%marc%'
Above query will print all proc name which contain marc .

Let me if u have any problem.
Teradata Employee

Re: Table Query

If the text of the Stored Procedure was saved in the database when it was compiled, it can be retrieved via SHOW PROCEDURE. It's not stored in the DBC dictionary tables but in a special "subtable" of the SP database object.
Teradata Employee

Re: Table Query

Teradata Meta Data Services (MDS) provides extensive information for Stored Procedures including the request text used to define the SP, parameters, as well as identify if the SP references other SPs, tables, views, columns, macros, hash indexes, join indexes, UDFs.