Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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

1 Answer 1

up vote 4 down vote accepted
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';
share|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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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