Archives of the TeradataForum

Message Posted: Mon, 01 Jul 2002 @ 19:51:14 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

We plan on using metadata services (MDS) to provide us with view to table mappings. We decided to create our own metadata repository, (right or wrong) and using MDS as just another metadata source system. After moving the table and view databases to MDS, you would have to do some digging in the MDS tables to find the one that maps view columns to table columns. (the tables are created using dynamic names)

The following macro was done by one of our programmers to map tables to all cases where the column is used. This is the opposite direction from what you asked but it should help. Note the names of the tables, such as metaclass_1005 which are created when you load the MDS product. I suspect the names are created on the fly so yours will probably be different but you can root through them to find the tables.

replace macro metadata.column_info(column_nm varchar(100),table_nm
varchar(100),database_nm varchar(100))
as
(
sel
view_info.name,
db_info.name
from      metadata.metaclass_1005 view_info
          ,metadata.metaclass_1002 db_info
          ,(sel originid,destinationid from metadata.metarelation_1113)b(origin_id,destination_id)
          ,(sel x.loid from metadata.metaclass_1004 x,metaclass_1003 y,metadata.metaclass_1002 z
                   where
                   x.databaseid=y.databaseid
                   and x.tableid=y.tableid
                   and y.databaseid = z.databaseid
                   and trim(lower(x.name)) = :column_nm
                   and trim(lower(y.name)) = :table_nm
                   and trim(lower(z.name)) = :database_nm
          )a(column_id)
where     b.destination_id = a.column_id
and       view_info.loid = b.origin_id
and       view_info.databaseid = db_info.databaseid;);

	<Prev	Next>		<<First	<Prev	Next>	Last>>