Query to find out the table name and the columns of that table associated with a view

Database
Enthusiast

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 empid
view1 employee ename employeename

Thanks in advance
14 REPLIES
gg
Enthusiast

Re: Query to find out the table name and the columns of that table associated with a view

You could try to parse the output of:
show select * from view1;

Also check http://www.teradataforum.com/teradata/20040102_143148.htm
Enthusiast

Re: Query to find out the table name and the columns of that table associated with a view

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
Enthusiast

Re: Query to find out the table name and the columns of that table associated with a view

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 as
select
coalesce(t1.col1, v2.col2,'x') as v_col4
from view2 v2
left outer join (select max(col1) as col1 from table1 group by 1) t1
on v2.col3 = t1.col3
;
Enthusiast

Re: Query to find out the table name and the columns of that table associated with a view


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 C
WHERE T.Databasename=C.Databasename
AND T.Tablename=C.Tablename AND
RequestText LIKE '%Databasename.tablename%' ORDER BY 1,2
Enthusiast

Re: Query to find out the table name and the columns of that table associated with a view

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 t

For 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_VIEW
AS 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 ,V

But we cannot get answerset like 'EDW_DATA,Charge_TR22,T' unless we try to do some anaysis on REQUESTTEXT ..

cheers,
Novice..
Teradata Employee

Re: Query to find out the table name and the columns of that table associated with a view

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

Re: Query to find out the table name and the columns of that table associated with a view

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
Junior Contributor

Re: Query to find out the table name and the columns of that table associated with a view

SHOW TABLE x

HELP TABLE x

HELP COLUMN x.*

SELECT * FROM dbc.TablesV

WHERE DatabaseName = 'y' AND TableName = 'x'

Dieter

Enthusiast

Re: Query to find out the table name and the columns of that table associated with a view

Thanks Dieter.