All Forums UDA
marcmc 112 posts Joined 12/05
16 Feb 2007
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_Marcsp_Marc1Is 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.

marcmc 112 posts Joined 12/05
21 Feb 2007

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.

rakesh bhamare 5 posts Joined 08/06
07 Mar 2007

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.

Fred 1096 posts Joined 08/04
07 Mar 2007

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.

Triton 18 posts Joined 05/08
28 Jan 2011

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.

You must sign in to leave a comment.