getting table and column names from a query in teradata

Tools
Fan

getting table and column names from a query in teradata

Hi All,

I have SQL Queries in teradata, from which I need to reverse engineer and get the table names and column names. To explain more, please consider the following example:

Query

SELECT NAME, AGE, DEPT FROM EMPLOYEE EMP, DEPARTMENT DEP WHERE EMP.EMPID=DEP.EMPID

What I need is if there is any tool available, where in, if I paste the above query, I should get the output as given below:

TABLE NAME(S)              COLUMN NAME(S)
~~~~~~~~~~              ~~~~~~~~~~~~~~

EMPLOYEE                            NAME
EMPLOYEE                            AGE
DEPARTMENT                       DEPT

If there is no tool available as such to get the above result, can anyone suggest some work around to get the above done?

Thanks in advance.

Regards,

Vinod K
5 REPLIES
Enthusiast

Re: getting table and column names from a query in teradata

I think Atanasuite will give you the tables referenced by a query, including those in any views referenced by the query, but probably not the columns.

If I had to do a lot of these, I think I would p@rse out the column and table/view names and put them in a table(s), then use SQL to do a lookup against the dicitionary tables.

for each query, you would need to store the columns, tables, and aliases. If aliases aren't used, then the column can only be in one table in the query. If aliases are used, then you know what the table is.

Maybe something like this:

query
-----------
query_iD
sql_txt

QueryColums
-------------
query_id
column_nm
alias_nm
table_nm

QueryTables
-------------
query_id
table_nm
alias_nm

Select qc.query_id
, qc.column_nm
, qt.table_nm
from querycolumns qc
inner join query_tables qt
on qc.query_id = qt.query_id
and (qc.alias_nm = qt.alias_nm
or
qc.table_nm = qc.table_nm)
join dbc.columns c
on qc.column_nm = c.columnname
and qt.table_nm = c.tablename
and databasename = database
;

At least I think that will work. I haven't tried it.
You would have to p@rse the column and table names from the query. You would probably need some code to do this.
Fan

Re: getting table and column names from a query in teradata

Hi,

Thank you for your reply. I will try this out.

Regards,

Vinod K
Enthusiast

Re: getting table and column names from a query in teradata

Dictionary tables and views are wonderful source of information to understand what is there in the database. In this case dbc.tables and dbc.columns will serve your requirement whether you pass tablenames and columnnames manually or through parameters to the SQL. Watch out for views with the same column names because most of the warehouse sites, for the purpose of security and locking issues create views on top of tables using the same column names.
Fan

Re: getting table and column names from a query in teradata

Thank you Ramakrishna Vedantam,

Will try out that.

Regards,

VInod K
Enthusiast

Re: getting table and column names from a query in teradata

We have query logging turned on and I use the DBQLObjTbl and DBQLogTbl to access the components of a query. This provides a pretty comprehensive break down. (I use to to scan for combinations of confidential fields)

If you don't have logging turned on you might be able to get creative by parsing dbc.AccessLog in the StatementText column.