Determine primary keys and unique keys for all tables in a database…


With SQL Server 2005, there are new ways to obtain richer metadata in a database and more efficiently. We have introduced new catalog views that exposes all the metadata that SQL Server uses and can be created by various DDL statements. The older ANSI SQL style INFORMATION_SCHEMA views are also still available if you want to write portable queries. I already posted a tip about finding dependencies http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx between various objects using the new catalog views. Here are two queries that show you how to retrieve primary/unique key details for all tables in a database:

 

— ANSI SQL compatible and works from SQL70 onwards:

select kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
  join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
    on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
   and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
   and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
   and kcu.TABLE_NAME = tc.TABLE_NAME
 where tc.CONSTRAINT_TYPE in ( ‘PRIMARY KEY’, ‘UNIQUE’ )
 order by kcu.TABLE_SCHEMA, kcu.TABLE_NAME
, tc.CONSTRAINT_TYPE, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION;

 

— SQL Server 2005 specific:

select s.name as TABLE_SCHEMA, t.name as TABLE_NAME

     , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
     , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
  from sys.key_constraints as k
  join sys.tables as t
    on t.object_id = k.parent_object_id
  join sys.schemas as s
    on s.schema_id = t.schema_id
  join sys.index_columns as ic
    on ic.object_id = t.object_id
   and ic.index_id = k.unique_index_id
  join sys.columns as c
    on c.object_id = t.object_id
   and c.column_id = ic.column_id
 order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, ORDINAL_POSITION;

Comments (7)

  1. Chris says:

    Great! Thanks! This is exactly what I was looking for. I like that it just works and I don’t have to understand it 😉

  2. Manfred Mejías says:

    With Sqlserver 2000 we have a little problem if we don’t use dbo user.  For example, we have two user with SQL Server authentication, Manfred and Alexis and create the "Sales" database.  So, we have the table Deparment in each user.  If the user dbo uses the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view it don’t see the primary keys, but, if we use the user Manfred we can see it.

    We edit the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view and changed one line index_col(t_obj.name,i.indid,v.number) with index_col(user_name(t_obj.uid)+’.’+t_obj.name,i.indid,v.number) and it works, why do you think?

  3. sqletips says:

    The INFORMATION_SCHEMA.KEY_COLUMN_USAGE is a known issue / bug for SQL Server 2000. We have fixed it in SQL Server 2005. You will have to workaround it in SQL Server 2000 by writing queries against the system tables instead.



    Umachandar

  4. Manfred Mejías says:

    thks :o)