All Forums Database
Yunfei Zhao 8 posts Joined 08/15
27 Aug 2015
how to find the unique column(s) in a table

I am a user of Teradata, and not a developer. I run the select statement to do my work. I believe that each table has a unique column (like 1 column SSN), or a unique combintion of coumns(like 4 columns Firstname, Lastname, DOB, ZIPCODE). I need the unique columns to connect different tables(joins). Quite often, I work on tables that have hundreds of columns, and I do not know what is the unique column(s) of that table.
How to find the unique column(s) in a table? The statement (show select * from table) might help, but it returns a lot of information, and I do not know how to get the answer to my question.
 
Thanks
Yunfei
 
 

muthu1802 20 posts Joined 01/12
27 Aug 2015

you can get it from one of the dbc tables called indices. use below query to get unique columns available in a specified table
 

select 
   columnname 
from 
   dbc.indices 
where 
   tablename=<<table_name>> 
   and databasename=<<database_name>> 
   and uniqueflag='Y' ;

 
 
 

Yunfei Zhao 8 posts Joined 08/15
28 Aug 2015

Thanks a lot. This is really helpful. For my case, either those tables do not have index, or those are actually views and not tables. The queries returns 0 rows. But thanks the same.

kirthi 65 posts Joined 02/12
28 Aug 2015

Use Show View statements to find the underlying tables, then please apply the above SQL to find the unique columns in those tables.

krishaneesh 140 posts Joined 04/13
28 Aug 2015

if they are views withing views, the actual table may not show up. in such case do a "show select * from databasename.viewname". This gives the definitions of all underlying views and tables.

You must sign in to leave a comment.