Looking for View Definition in DBC table

Database

Looking for View Definition in DBC table

For some reason I am unable to access "teradata adminstrator" to see the view definition.

Is there a DBC table to look at the view definition?

TIA
11 REPLIES
Enthusiast

Re: Looking for View Definition in DBC table

Just use command -> show view
Enthusiast

Re: Looking for View Definition in DBC table

Sorry, that's -> show view databasename.viewname;

Re: Looking for View Definition in DBC table

Hi,

Even the below command will give u the definition
Sel * from dbc.tables where tablename ' 'xyz' and databasename ' 'abc'
This command will give all the attributes of the view (view definition is also one of them).
I hope this helps
Enthusiast

Re: Looking for View Definition in DBC table

sel CreateText
from dbc.tvm
where
TVMName = 'zxcv' -- ur view name

by this query u can view the definition..
provided u ve access to dbc tables and views.

Re: Looking for View Definition in DBC table

Thankq..
Enthusiast

Re: Looking for View Definition in DBC table

What is the difference between requesttext and createtext?
Junior Contributor

Re: Looking for View Definition in DBC table

RequestText is the original source code "as is" including comments:
SHOW SELECT * FROM VIEW

When a view is created then this source code is parsed, object names are resolved and stored in CreateText:
SHOW QUALIFIED SELECT * FROM VIEW

Dieter

Re: Looking for View Definition in DBC table

sorry for digging up the old topic..

can somebody please let me know from where is the old view definition (database and columns) is fetched when I execute

SHOW QUALIFIED SEL * FROM VIEW ??

In dbc.tables and dbc.tvm, I can only see the statement used to create the view like 'replace view as lock row for access sel * from table', but when i use qualified I can see all the columns that were there when the view was created..

I'm just curious where the old information is still available..

-Prashanth

Enthusiast

Re: Looking for View Definition in DBC table

when  QUALIFIED is used (in this case) ,Teradata replace * with fully qualified actual column names of base table. As a result you are viewing the column names in  view definition .