첨부 실행 코드는 나눔고딕코딩 폰트를 사용합니다.
유용한 소스 코드가 있으면 icodebroker@naver.com으로 보내주시면 감사합니다.
블로그 자료는 자유롭게 사용하세요.

■ 데이터베이스 스키마 조회하기

------------------------------------------------------------------------------------------------------------------------

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;

------------------------------------------------------------------------------------------------------------------------

Posted by 사용자 icodebroker

댓글을 달아 주세요