[DATABASE/SQLSERVER] 데이터베이스 스키마 조회하기

■ 데이터베이스 스키마를 조회하는 방법을 보여준다.

▶ 예제 코드 (SQL)


SELECT
    UPPER(A.NAME)      AS [테이블영문명]
   ,B.COLORDER         AS [컬럼순번]
   ,UPPER(B.NAME)      AS [컬럼영문명]
   ,UPPER(C.NAME)      AS [자료형]
   ,B.LENGTH           AS [길이]
   ,ISNULL(B.SCALE, 0) AS [정밀도]
   ,ISNULL
    (
        'PK' +
        CONVERT
        (
            VARCHAR(10),
            (
                SELECT SC.KEYNO
                FROM       SYSINDEXES   AS SA WITH(NOLOCK)
                INNER JOIN SYSOBJECTS   AS SB WITH(NOLOCK) ON  SB.ID    =  SA.ID
                                                           AND SB.XTYPE =  'U'
                                                           AND SB.ID    =  A.ID
                INNER JOIN SYSINDEXKEYS AS SC WITH(NOLOCK) ON  SC.ID    =  SA.ID
                                                           AND SC.INDID =  SA.INDID
                INNER JOIN SYSCOLUMNS   AS SD WITH(NOLOCK) ON  SD.ID    =  SC.ID
                                                           AND SD.COLID =  SC.COLID
                                                           AND SD.COLID =  B.COLID
                                                           AND SD.ID    =  B.ID
                WHERE SA.INDID = 1
            )
        ),
        ''
    )                  AS [PK]
   ,(
        CASE B.ISNULLABLE
            WHEN 0 THEN ''
            WHEN 1 THEN 'NULL'
        END
    )                  AS [NULL]
   ,ISNULL(E.TEXT, '') AS [DEFAULT]
FROM      SYSOBJECTS     AS A WITH(NOLOCK)
LEFT JOIN SYSCOLUMNS     AS B WITH(NOLOCK) ON  B.ID    =  A.ID
LEFT JOIN SYSTYPES       AS C WITH(NOLOCK) ON  C.XTYPE =  B.XTYPE
                                           AND C.NAME  <> 'SYSNAME'
LEFT JOIN SYSCONSTRAINTS AS D WITH(NOLOCK) ON  D.ID    =  A.ID
                                           AND D.COLID =  B.COLID
LEFT JOIN SYSCOMMENTS    AS E WITH(NOLOCK) ON  E.ID    =  D.CONSTID
WHERE A.XTYPE = 'U'
ORDER BY A.NAME     ASC,
         B.COLORDER ASC,
         B.NAME     ASC;

SELECT

UPPER(A.NAME) AS [테이블영문명]

,B.COLORDER AS [컬럼순번]

,UPPER(B.NAME) AS [컬럼영문명]

,UPPER(C.NAME) AS [자료형]

,B.LENGTH AS [길이]

,ISNULL(B.SCALE, 0) AS [정밀도]

,ISNULL

(

'PK' +

CONVERT

(

VARCHAR(10),

(

SELECT SC.KEYNO

FROM SYSINDEXES AS SA WITH(NOLOCK)

INNER JOIN SYSOBJECTS AS SB WITH(NOLOCK) ON SB.ID = SA.ID

AND SB.XTYPE = 'U'

AND SB.ID = A.ID

INNER JOIN SYSINDEXKEYS AS SC WITH(NOLOCK) ON SC.ID = SA.ID

AND SC.INDID = SA.INDID

INNER JOIN SYSCOLUMNS AS SD WITH(NOLOCK) ON SD.ID = SC.ID

AND SD.COLID = SC.COLID

AND SD.COLID = B.COLID

AND SD.ID = B.ID

WHERE SA.INDID = 1

)

) AS [PK]

CASE B.ISNULLABLE

WHEN 0 THEN ''

WHEN 1 THEN 'NULL'

END

) AS [NULL]

,ISNULL(E.TEXT, '') AS [DEFAULT]

FROM SYSOBJECTS AS A WITH(NOLOCK)

LEFT JOIN SYSCOLUMNS AS B WITH(NOLOCK) ON B.ID = A.ID

LEFT JOIN SYSTYPES AS C WITH(NOLOCK) ON C.XTYPE = B.XTYPE

AND C.NAME <> 'SYSNAME'

LEFT JOIN SYSCONSTRAINTS AS D WITH(NOLOCK) ON D.ID = A.ID

AND D.COLID = B.COLID

LEFT JOIN SYSCOMMENTS AS E WITH(NOLOCK) ON E.ID = D.CONSTID

WHERE A.XTYPE = 'U'

ORDER BY A.NAME ASC,

B.COLORDER ASC,

B.NAME ASC;