Tables to Views Cross Reference

Database
Enthusiast

Re: Tables to Views Cross Reference

Oops it does :o , I over looked the multiple result tabs of the queryman ;-)

May be I should start working on the script now :-)
Teradata Employee

Re: Tables to Views Cross Reference

Teradata Meta Data Services (MDS) provides exactly what you need. MDS can be easily installed & configured on a client PC, laptop, or Linux system.

You then select the Teradata database(s) that you want MDS to track in the metadata repository and it goes off and reads the Teradata Data Dictionary and obtains all of the information for views including identifying all base tables referenced, nested views, titles for view columns, and it even stores the RequestText used to define a view.

You just need to make sure that you ask MDS to load information for all databases that contain the base tables into the MDS repository. Otherwise MDS will not be able to resolve all references for a view.

Once all of the metadata is in the repository you can easily find out for a given table what view(s) reference it or conversely for a given view what table(s) does it reference, as well as nested view information.
Not applicable

Re: Tables to Views Cross Reference

Thanks for the information. I just wanna checkout this Teradata Meta Data Services.
Highlighted
Enthusiast

Re: Tables to Views Cross Reference

I need exactly the same SQL to run against DBC.Tables.  Trying to find all the base views containing a table and the views based off the base views.

Other than enabling MDS in Teradata, does anyone have sample recursive SQL for this?

Enthusiast

Re: Tables to Views Cross Reference

I don't know if it were me ,I'd prolly run below sql three four ( with output being added to input everytime ) times or build recursive logic  for the same.But usually you will have only 3-4 layers of view in which case you can fire query 2-3 times and get deisred results

SEL DATABASENAME,TABLENAME

FROM DBC.TABLES

WHERE REQUESTTEXT LIKE ANY

(SEL '%' || TABLENAME || '%'

FROM DBC.TABLES

WHERE TABLENAME IN

()

)

;

Enthusiast

Re: Tables to Views Cross Reference

Note : Will have to add quotes and trim tablename in subquery for it to work