how to find the unique column(s) in a table

Database
Enthusiast

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

4 REPLIES
Enthusiast

Re: how to find the unique column(s) in a table

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' ;
Enthusiast

Re: how to find the unique column(s) in a table

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.

Enthusiast

Re: how to find the unique column(s) in a table

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

Enthusiast

Re: how to find the unique column(s) in a table

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.