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.

Is there a query that will do that?

I found some queries that can do this for one table, but I wasn't able to modify it so I can see:

tablename | column | type
share|improve this question

2 Answers 2

up vote 6 down vote accepted

Something like this:

select tc.table_schema, tc.table_name, kc.column_name
from 
    information_schema.table_constraints tc
    join information_schema.key_column_usage kc 
        on kc.table_name = tc.table_name and kc.table_schema = tc.table_schema
where 
    tc.constraint_type = 'PRIMARY KEY'
    and kc.position_in_unique_constraint is not null
order by tc.table_schema,
         tc.table_name,
         kc.position_in_unique_constraint;
share|improve this answer

This is more accurate answer:

select tc.table_schema, tc.table_name, kc.column_name 
from  
    information_schema.table_constraints tc,  
    information_schema.key_column_usage kc  
where 
    tc.constraint_type = 'PRIMARY KEY' 
    and kc.table_name = tc.table_name and kc.table_schema = tc.table_schema
    and kc.constraint_name = tc.constraint_name
order by 1, 2;

You missed the and kc.constraint_name = tc.constraint_name part, so it lists all constraints.

share|improve this answer
    
While your query works, the more important difference is the missing and kc.position_in_unique_constraint is not null part. And you are strongly encouraged to use ANSI JOINs (while many consider it being a matter of taste). –  dezso Nov 4 '12 at 18:55

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.