Tables to Views Cross Reference

[joseebasa]

I've been task with finding all the views that use a set group of tables. I also need to find any views that are build off of other views (i.e. it will require some kind of recursive logic).

Has anyone done this in Teradata?

I've looked through the forum but couldn't find anything that resembled what I need.

Any information is greatly appreciated.

[joedsilva]

One of my "to do" list include dumping the output of

explain select * from mydb.mytablesview and parsing the output for getting referenced tables using shell script .

Have been a lazy year so far though ;-) so waiting for someone to do that for me.

[Fred]

Check out Teradata Meta Data Services. The good news is that it's supplied free with your Teradata system. The bad news is you need to provide a Windows server, and setup and administration is not trivial. But then, neither is writing your own p****r...

[Rbar]

-- to find table references in views
sel * from DBC.tvm
where tablekind = 'V' and RequestText like '%table_name%'

[BBR2]

Easiest of all (manual) is to right click the view in Teradata administrator and select references. It should list all the objects that are being used in the underlying view.

For purpose of automation you will need to p a r s e the entire DDL.

Thanks,
Vinay Bagare

[Rbar]

Teradata Admin won't resolve views to the underlying tables unless you run explains. You'd already have to know which views to check, but if you knew those, you wouldn't have to search any DDL.
Querying DBC.tvm.RequestText is a way to search all the DDL for explicit table name references only.
Metadata services or other 3rd party xref tools have a reason for existing.

[rgs]

Joe,

It might be easier to use a show:

show select * from mydb.mytablesview;

It provides more information too since all the refrenced DDL is dumped, including UDFs when used.

[rgs]

Probably using DBC.TableText[X] view is the better choice since it gets the entire request text. If it is too long it is not in TVM but in dbc.TextTbl. And it might be multiple rows in that table.

See Data Dictionary document for more information. Here is an example from the documentation:

SELECT TableName,TableKind, LineNo, RequestText( char(50)) FROM
DBC.TableText WHERE DatabaseName = 'Personnel' ORDER BY TableName,
LineNo;

The shown example only returns the first 50 characters if each row.

[joedsilva]

Hmm... There's the trouble with nested views ... ;-)

CREATE VIEW V_TABLE_CNT
AS
SELECT COUNT(*) AS TABLE_CNT FROM DBC.TABLES
;

SHOW SELECT * FROM V_TABLE_CNT;

Request Text
CREATE VIEW V_TABLE_CNT
AS
SELECT COUNT(*) AS TABLE_CNT FROM DBC.TABLES;

[rgs]

Hmmm .. what release? My output:

show select * from v_Table_cnt;

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

---------------------------------------------------------------------------
CREATE SET TABLE DBC.tvm ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT

{snip}

*** Text of DDL statement returned.
---------------------------------------------------------------------------
CREATE SET TABLE DBC.Dbase ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT

{snip}

*** Text of DDL statement returned.
---------------------------------------------------------------------------
REPLACE VIEW DBC.Tables
AS
SELECT CAST(TRANSLATE(dbase.DatabaseName USING UNICODE_TO_LOCALE WITH ERRO
R)
AS CHAR(30)) (NAMED DatabaseName),
CAST(TRANSLATE(tvm.TVMName USING UNICODE_TO_LOCALE WITH ERROR)
AS CHAR(30)) (NAMED TableName),

{snip}

*** Text of DDL statement returned.
---------------------------------------------------------------------------
CREATE VIEW V_TABLE_CNT
AS
SELECT COUNT(*) AS TABLE_CNT FROM DBC.TABLES
;

this is output from 12.0 beta, but that should not make any differnce, since this has been available for a long time.