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;
'연구개발 > DBA' 카테고리의 다른 글
SS2000 - SQL Agent Job 정보 조회 (0) | 2010.07.27 |
---|---|
SS2000 - DBO 권한 사용자 조회 (0) | 2010.07.27 |
SS2000 - Identity 컬럼 최대값 모니터링 (0) | 2010.07.27 |
SS2000 - 현재 캐시된 내용 조회하기 (0) | 2010.07.27 |
디스크 사이즈 정보 읽어오는 스크립트 (0) | 2010.07.27 |