How to alter the output for the sql desc command

sqlplus

When I do desc table, I get back the columns Name, Null? and Type. What I also wish to get back is a column like UNIQUE to tell me if the column is in fact unique. I realize that this is a combination of columns labeled UNIQUE with the column (if present) labeled as the primary key for that table.

As an alternative, I have tried:

SELECT 
column_name "Name", 
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE table_name='TABLE_NAME_TO_DESCRIBE';

which I found somewhere, however I get 0 rows returned when I replace the TABLE_NAME_TO_DESCRIBE with the name of my table.

I am running from sqlplus. Any way to accomplish this? Either specific (returning of UNIQUE constraint) or general (how to alter desc command results) answers appreciated.

Best Answer

SELECT utc.column_name "Name"
   , utc.nullable "Null?"
   , concat(concat(concat(utc.data_type,'('),utc.data_length),')') "Type"
   , ui.uniqueness
FROM user_tab_columns utc 
LEFT JOIN user_ind_columns uic ON uic.table_name = utc.table_name 
   AND utc.column_name = uic.column_name
LEFT JOIN user_indexes ui ON ui.table_name = utc.table_name
   AND uic.index_name=ui.index_name
WHERE utc.table_name='TABLE_NAME_TO_DESCRIBE';
Related Question