All Forums Database
29 May 2009
Query to find out the table name and the columns of that table associated with a view

Can we frame a query to find out the original table name and the original column names of that table associated with a teradata view.I was unable to figure out, what dbc tables/view are to be used collectively to get the result.For example, if the view is:create view view1 as select empid as eid, employeename as ename from employee;Can we frame a query to find out that "employee" is the table name and "empid","employeename" are the columns of the table?Expected output is something like this : View Name TableName ViewColumn TableColumn------------------------------------------- ---------------view1 employee eid empidview1 employee ename employeenameThanks in advance

gg 12 posts Joined 10/04
29 May 2009

You could try to parse the output of:show select * from view1;Also check http://www.teradataforum.com/teradata/20040102_143148.htm

01 Jun 2009

That's really a surprise that teradata does not store this information in any dbc tables.I would have to write an entire sql parser for this reason or customize an open source sql parser to suite my requirement.Some times, the requirement looks quite simple, but the work needed to be done to accomplish that is so much.That's so sad.Any other ideas from you guys?Thanks for the help Georg.

gg 12 posts Joined 10/04
02 Jun 2009

I think your requirement is not so simple and I doubt any other db can offer this out of the box.Your example is quite simplistic but views can be much more complex and consist of nested views with aggregatioins a.s.o.e.g.:replace view view1 asselect coalesce(t1.col1, v2.col2,'x') as v_col4from view2 v2left outer join (select max(col1) as col1 from table1 group by 1) t1 on v2.col3 = t1.col3;

lathavim 7 posts Joined 05/09
04 Jun 2009

The below query will give you all the view & column names associated with a table. Substitute your databasename & tablename in the 'where condition' and see if this works.SELECT T.DatabaseName,T.tablename,TableKind AS ObjectType ,C.Columnname FROM dbc.tables T, dbc.COLUMNS CWHERE T.Databasename=C.DatabasenameAND T.Tablename=C.Tablename AND RequestText LIKE '%Databasename.tablename%' ORDER BY 1,2

novice 38 posts Joined 07/07
08 Jun 2009

Hi Latha,I think your solution will not work and what Gorge has said is the way to go.The DBC.Tables.RequestText will give you the DDL used to create View or Table but to extract exact "View name" and then tFor example CREATE SET TABLE EDW_DATA.CHARGE_TR22 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( CHARGE_UID VARCHAR(36) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, NAME VARCHAR(150) CHARACTER SET LATIN NOT CASESPECIFIC ) PRIMARY INDEX ( CHARGE_UID );REPLACE VIEW D290267.CHARGE_TR22_VIEWAS LOCKING ROW FOR ACCESS SELECT CHARGE_UID (TITLE 'CHARGE IDentification ') FROM EDW_DATA.CHARGE_TR22;Now SEL DatabaseName,tablename,TableKind FROM DBC.TABLES WHERE REQUESTTEXT LIKE '%EDW_DATA.CHARGE_TR22%' gives you something like d290267,Charge_TR22_View ,VBut we cannot get answerset like 'EDW_DATA,Charge_TR22,T' unless we try to do some anaysis on REQUESTTEXT ..cheers,Novice..

Fred 1096 posts Joined 08/04
09 Jun 2009

The Teradata "Meta Data Services" component (included with "Teradata Utility Pack") includes the necessary parsing logic to document these dependencies.

gatpasrikanth 10 posts Joined 11/11
30 Nov 2011

how to find the data type of all columns. example i have 100 coulmns in table. i want know whether they are numeric or character types.thanks

dnoeth 4628 posts Joined 11/04
30 Nov 2011

SHOW TABLE x

HELP TABLE x

HELP COLUMN x.*

SELECT * FROM dbc.TablesV
WHERE DatabaseName = 'y' AND TableName = 'x'

Dieter

Dieter

gatpasrikanth 10 posts Joined 11/11
01 Dec 2011

Thanks Dieter.

 

gatpasrikanth 10 posts Joined 11/11
01 Dec 2011

Hi Dieter,

But my problem did not slove with the above options you mentioned.

genrally we get column information by clicking on tools --->list columns in teradata to know the datatypes.

 

I need a code where in it should display all the columns names and datatype of each coulmn.

in this HELP TABLE x var type is showing as question mark(?).

Please help me out.

 

 

dnoeth 4628 posts Joined 11/04
01 Dec 2011

Explain a HELP TABLE and you'll see it accessing dbc.tvfields.

You used HELP TABLE on a view. When a view is created only the column names are resolved and stored within the system tables, but no other info like datatypes.

For views you should use HELP COLUMN tab.*, then the optimizer will resolve the view down to the used base tables and display the datatypes properly.

Tools - List Columns probably uses an ODBC/.NET call to resolve the datatypes.

Dieter

 

 

Dieter

gatpasrikanth 10 posts Joined 11/11
01 Dec 2011

Thnaks so much Dieter. I got the desired results with the HELP COLUMN tab.*.

I got it like CF,I ,I2,D datatypes for variables.

Dieter:is CF stands for char fixed rt...?

 

Thanks!!

Sreekanth

dnoeth 4628 posts Joined 11/04
01 Dec 2011

CF = CHAR

CV = VARCHAR

I = INTEGER

I2 = SMALLINT

D = DECIMAL

You'll find all possible values in the manuals:

SQL Data Definition Language Detailed Topics: HELP COLUMN

Dieter

Dieter

jhaamresh 3 posts Joined 07/12
23 Jul 2012

Mab this this would be useful..

SELECT 
DATABASENAME,
TABLENAME,
COLUMNNAME,
TRIM(COLUMNTYPE)||'('||TRIM(COLUMNNUM)||')'
FROM (
SELECT 
DATABASENAME,
TABLENAME,
COLUMNNAME,
CASE WHEN COLUMNTYPE='CF' THEN 'CHAR'
	 WHEN COLUMNTYPE='CV' THEN 'VARCHAR'
	 WHEN COLUMNTYPE='D' THEN 'DECIMAL'	 
	 WHEN COLUMNTYPE='TS' THEN 'TIMESTAMP'	 	 
	 WHEN COLUMNTYPE='I' THEN 'INTEGER'
	 WHEN COLUMNTYPE='I2' THEN 'SMALLINT'
	 WHEN COLUMNTYPE='DA' THEN 'DATE'
END AS COLUMNTYPE,
CASE WHEN COLUMNTYPE='CF' THEN COLUMNLENGTH
	 WHEN COLUMNTYPE='CV' THEN COLUMNLENGTH
	 WHEN COLUMNTYPE='D' THEN (DECIMALTOTALDIGITS||','||DECIMALFRACTIONALDIGITS)
	 WHEN COLUMNTYPE='TS' THEN COLUMNLENGTH	 	 
	 WHEN COLUMNTYPE='I' THEN DECIMALTOTALDIGITS
	 WHEN COLUMNTYPE='I2' THEN DECIMALTOTALDIGITS
	 WHEN COLUMNTYPE='DA' THEN NULL
END AS COLUMNNUM
FROM DBC.COLUMNS
WHERE DATABASENAME='<database name>' ) TBL
You must sign in to leave a comment.