Table Fields Data

UDA
Enthusiast

Table Fields Data

Apologies if this is in the wrong forum or has been asked somewhere else before.

If I was asked "Can you please check what tables contain the following field %product_id%?"

Is there a utility to do this?
5 REPLIES
Enthusiast

Re: Table Fields Data

You can query the view dbc.columns to identify the fields belonging to a particular table.

For eg:-

select columnname,tablename from dbc.columns where columnname like ('a%') sample 5;

*** Query completed. 5 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

ColumnName TableName
------------------------------ ------------------------------
AcrIndex AccLogRuleTbl_V2R2
AcrCreExtProcedure AccLogRuleTbl
AccountName AccountInfo
AcrDump AccLogRuleTbl_V2R5
AcrDelete AccLogRuleTbl_V2R4
Enthusiast

Re: Table Fields Data

Thanks that's great.

What about if i wanted to see every stored procedure that referenced '%product_id%'
Enthusiast

Re: Table Fields Data

Not very sure about what you are really looking for...

If you want to see what parameters are defined in Create Procedure, then you can get the information from dbc.columns view. I tried creating a procedure with single parameter deptid . Dbc.columns showed me the column when I submitted the below query

select * from dbc.columns where tablename='TESTING';

*** Query completed. One row found. 29 columns returned.
*** Total elapsed time was 1 second.

DatabaseName TableName ColumnName
------------------------------ ------------------------------ -------------
Leo TESTING DEPTID

show procedure testing;

*** Text of DDL statement returned.
*** Total elapsed time was 1 second.

---------------------------------------------------------------------------
CREATE PROCEDURE TESTING (DEPTID VARCHAR(5))
BEGIN
declare dept_name varchar(30);

SELECT dept_id into :dept_name FROM DEPARTMENTS A WHERE A.DEPT_ID=EPTID
;
END;

Looks to me you are expecting something different. I presume that you want to identify all the tables whose columns are referenced in the stored procedure.if that is the case then probably you will have to investigate more on how to acheive your goal.The best method I can think of is to do a pattern search on SP definition.
Enthusiast

Re: Table Fields Data

Thanks again,

Doing a pattern search on a Stored Procedure definition is exactly what I want to do. I have not been able to do it. I thought the RequestText or CommentString columns from DBC.Tables would help but they do not.

For example, if I needed to change all stored procedure variables that contained the
string '%product_id%' from varchar(15) to varchar(20) then I would need to identify all the Stored Procedures where I would need to do this. I have almost 100 stored procedures! I have been evaluating a product called Teradata Meta Data Services to do this but it has nt been fruitful yet.

The show procedure command will show me the definition of a stored procedure which surely means that the definition is held on the database(the same applies for the show definition of a Stored Procedure in Teradata Administrator). I am looking for where this data is coming from.

Enthusiast

Re: Table Fields Data

One way of doing this ..

Extract SHOW PROCEDURE to a flat file.
Load this into a Teradata table using BTEQ.
Use POSITION function to find for the string in the table that you just loaded. For matches the POSITION function will return a value of 1 or greater than 1.

Thanks,
Vinay Bagare