All Forums UDA
marcmc 112 posts Joined 12/05
23 Aug 2007
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?

leo.issac 184 posts Joined 07/06
23 Aug 2007

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_V2R2AcrCreExtProcedure AccLogRuleTblAccountName AccountInfoAcrDump AccLogRuleTbl_V2R5AcrDelete AccLogRuleTbl_V2R4

marcmc 112 posts Joined 12/05
23 Aug 2007

Thanks that's great.What about if i wanted to see every stored procedure that referenced '%product_id%'

leo.issac 184 posts Joined 07/06
23 Aug 2007

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 queryselect * 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 DEPTIDshow procedure testing; *** Text of DDL statement returned. *** Total elapsed time was 1 second.---------------------------------------------------------------------------CREATE PROCEDURE TESTING (DEPTID VARCHAR(5))BEGINdeclare 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.

marcmc 112 posts Joined 12/05
24 Aug 2007

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 thestring '%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.

BBR2 96 posts Joined 12/04
30 Aug 2007

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

You must sign in to leave a comment.