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:

column_name "Name", 
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns

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.

  • TABLE_NAME_TO_DESCRIBE is case sensitive – Jack Douglas Sep 28 '11 at 15:48
  • Yeah, I was thinking that might be what was tripping me, but alas I get 0 rows either way with the query above. However, you did jog my mind and I see I was not logged in as a user with sufficient enough permissions. I now get 10 rows with that query, so that solves expanding the returned columns. However, I still do not see how I can list if a column is UNIQUE or not – demongolem Sep 28 '11 at 16:18
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
| improve this answer | |
  • I see, I have to ultimately make it over to the user_indexes table. Thanks! It is a nice extension of the standard desc available to me. – demongolem Sep 29 '11 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.