How to find list of database objects in teradata

Database

How to find list of database objects in teradata

Hello All,

I need to understand, how do we find the list of database objects, say Tables and Indexes in teradata as we have all_objects, user_objects and dba_objects in Oracle.

One way I got is, if teradata user is having an access to dbc.tables, dbc.indexes or dbc.TVM then we can find that out. But what in case new user is not having an access to these base tables? In Oracle using all_tables or user_tables we can easily identify that. Is there any such way available in teradata too?

Thanks in advance,
Mandar
6 REPLIES
Junior Contributor

Re: How to find list of database objects in teradata

Hi Mandar,
every Teradata user will have access to system views in dbc.

It depends on the dba which views are accessible, there are two sets, views with and without an appended X.
These are similar to Oracle's dba_xxx and user_xxx.:
X-views return info about objects the current user can access/has any rights on.
Non-X-views return info about *all* objects.

E.g. dba_objects -> dbc.tables, user_objects -> dbc.tablesX

If you're on TD12+ you should use the system views with an appended V, dbc.tablesV and dbc.tablesVX, the other ones are only for backward compatibility.

Dieter

Re: How to find list of database objects in teradata

Hello Dieter,

Thanks for the reply.

One more question, if dba do not grant select privileges to these views, then there will not be any way to find out the objects right ? Or still user itself has access to any particular view or table by which he can find that out ?

Thanks again,
Mandar

Re: How to find list of database objects in teradata

Also the point I missed out is access to dbc.TVM. I was trying to find out the list of indexes in particular user and for which it was required to join with dbc.TVM. New user was having access to dbc.tables but not for dbc.TVM. So is that dba's responsibility to grant access for it?

Thanks,
Mandar
Junior Contributor

Re: How to find list of database objects in teradata

Hi Mandar,
of course a dba could revoke all access rights to the system views, but this would be totally stupid :-)

Regarding dbc.tvm (and other system *tables*), it's similar to other DBMSes. You're not supposed to query metadata tables directly, you should use the predefined views.

Info about indexes is found in dbc.IndicesV, which already includes all the neccessary joins, no need to access dbc.TVM.

There's a "Data Dictionary" manual about all those dbc views.

Dieter

Re: How to find list of database objects in teradata

Thanks a Lot Dieter !!

I had never read about dbc.<>V tables. I will definetly go through it.

Thanks for your help !!

Regards,
Mandar

Re: How to find list of database objects in teradata

SELECT tablename, databasename FROM dbc.tables WHERE tablename LIKE '%TABLE/DATABASE%' ;