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

  • Article

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 https://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

  • Anonymous
    August 10, 2006
    Great! Thanks! This is exactly what I was looking for. I like that it just works and I don't have to understand it ;)
  • Anonymous
    September 14, 2006
    The comment has been removed
  • Anonymous
    September 14, 2006
    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
  • Anonymous
    September 14, 2006
    thks :o)
  • Anonymous
    June 18, 2009
    PingBack from http://patiosetsite.info/story.php?id=1006