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

■ 데이타베이스 테이블 컬럼 조회하기

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

SELECT

    A.TABLE_CATALOG                       AS [Database]

   ,A.TABLE_SCHEMA                        AS [Schema]

   ,A.TABLE_NAME                          AS [Table]

   ,CONVERT(INT, A.ORDINAL_POSITION)      AS Sequence

   ,A.COLUMN_NAME                         AS [Column]

   ,UPPER(A.DATA_TYPE)                    AS DataType

   ,ISNULL(A.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize

   ,ISNULL(A.CHARACTER_OCTET_LENGTH, 0)   AS ByteSize

   ,ISNULL(A.NUMERIC_PRECISION, 0)        AS NumericPrecision

   ,ISNULL(A.NUMERIC_PRECISION_RADIX, 0)  AS NumericPrecisionRadix

   ,ISNULL(A.NUMERIC_SCALE, 0)            AS NumericScale

   ,ISNULL(A.DATETIME_PRECISION, 0)       AS DateTimePrecision

   ,CASE A.IS_NULLABLE

        WHEN 'YES' THEN 'NULL'

        ELSE            ''

    END                                   AS [NULL]

   ,(

        SELECT

            CASE

                WHEN COUNT(*) > 0 THEN 'Y'

                ELSE                   'N'

            END

        FROM  syscolumns AS B

        JOIN  sysobjects AS C ON C.id = B.id

        WHERE B.status = 128

        AND   C.xtype  = 'U'

        AND   C.name   = A.TABLE_NAME

        AND   B.name   = A.COLUMN_NAME

    )                                     AS IsIdentity

   ,ISNULL(A.COLUMN_DEFAULT, '')          AS [Default]

   ,ISNULL(A.CHARACTER_SET_CATALOG, '')   AS CharacterSetCatalog

   ,ISNULL(A.CHARACTER_SET_SCHEMA, '')    AS CharacterSetSchema

   ,ISNULL(A.CHARACTER_SET_NAME, '')      AS CharacterSetName

   ,ISNULL(A.COLLATION_CATALOG, '')       AS CollationCatalog

   ,ISNULL(A.COLLATION_NAME, '')          AS [CollationName]

   ,ISNULL(A.DOMAIN_CATALOG, '')          AS DomainCatalog

   ,ISNULL(A.DOMAIN_SCHEMA, '')           AS DomainSchema

   ,ISNULL(A.DOMAIN_NAME, '')             AS DomainName

FROM  INFORMATION_SCHEMA.COLUMNS AS A

WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')

ORDER BY

    A.TABLE_NAME       ASC

   ,A.ORDINAL_POSITION ASC;

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

Posted by 사용자 icodebroker
TAG

댓글을 달아 주세요