[DATABASE/SQLSERVER] 데이터베이스 제약 조건 조회하기

■ 데이터베이스 제약 조건을 조회하는 방법을 보여준다.

▶ 예제 코드 (SQL)


SELECT
    A.TABLE_CATALOG      AS [Database]
   ,A.TABLE_SCHEMA       AS [Schema]
   ,A.TABLE_NAME         AS [Table]
   ,A.CONSTRAINT_CATALOG AS ConstraintDatabase
   ,A.CONSTRAINT_SCHEMA  AS ConstraintSchema
   ,A.CONSTRAINT_NAME    AS [Constraint]
   ,CASE A.CONSTRAINT_TYPE
        WHEN 'PRIMARY KEY' THEN 'PK'
        WHEN 'FOREIGN KEY' THEN 'FK'
        WHEN 'CHECK'       THEN 'CK'
        WHEN 'UNIQUE'      THEN 'UK'
        ELSE                    ''
    END                  AS ConstraintType
   ,CASE A.IS_DEFERRABLE
        WHEN 'YES' THEN 'Y'
        WHEN 'NO'  THEN 'N'
        ELSE            ''
    END                  AS IsDeferrable
   ,CASE A.INITIALLY_DEFERRED
        WHEN 'YES' THEN 'Y'
        WHEN 'NO'  THEN 'N'
        ELSE            ''
    END                 AS InitiallyDeferred
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS A
WHERE A.TABLE_NAME <> 'sysdiagrams'
ORDER BY
    A.TABLE_NAME      ASC
   ,A.CONSTRAINT_TYPE DESC;

SELECT

A.TABLE_CATALOG AS [Database]

,A.TABLE_SCHEMA AS [Schema]

,A.TABLE_NAME AS [Table]

,A.CONSTRAINT_CATALOG AS ConstraintDatabase

,A.CONSTRAINT_SCHEMA AS ConstraintSchema

,A.CONSTRAINT_NAME AS [Constraint]

,CASE A.CONSTRAINT_TYPE

WHEN 'PRIMARY KEY' THEN 'PK'

WHEN 'FOREIGN KEY' THEN 'FK'

WHEN 'CHECK' THEN 'CK'

WHEN 'UNIQUE' THEN 'UK'

ELSE ''

END AS ConstraintType

,CASE A.IS_DEFERRABLE

WHEN 'YES' THEN 'Y'

WHEN 'NO' THEN 'N'

ELSE ''

END AS IsDeferrable

,CASE A.INITIALLY_DEFERRED

WHEN 'YES' THEN 'Y'

WHEN 'NO' THEN 'N'

ELSE ''

END AS InitiallyDeferred

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS A

WHERE A.TABLE_NAME <> 'sysdiagrams'

ORDER BY

A.TABLE_NAME ASC

,A.CONSTRAINT_TYPE DESC;