데이터베이스의 Primary Key 정보 수집하기

PrimaryKey정보수집하기.sql

해당 데이터베이스의 모든 테이블들에 대한 Primary Key 정보를 읽어오는 스크립트입니다.

SQL 7.0 이상에서 이용 가능한 Information_Schema View를 이용한 형태와, SQL Server 2005의 DMV를 이용한 두 종류의 쿼리가 있습니다.

참고하시길...

-- ANSI SQL 호환 및 SQL 7.0 이상 적용 가능한 형태

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 용

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;