첨부 소스 코드는 나눔고딕코딩 폰트를 사용합니다.
728x90
반응형
728x170
SELECT
    A.CONSTRAINT_CATALOG                  AS ConstraintDatabase
   ,A.CONSTRAINT_SCHEMA                   AS ConstraintSchema
   ,A.CONSTRAINT_NAME                     AS [Constraint]
   ,A.TABLE_CATALOG                       AS [Database]
   ,A.TABLE_SCHEMA                        AS [Schema]
   ,A.TABLE_NAME                          AS [Table]
   ,CONVERT(INT, B.ORDINAL_POSITION)      AS Sequence
   ,A.COLUMN_NAME                         AS [Column]
   ,UPPER(B.DATA_TYPE)                    AS DataType
   ,ISNULL(B.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize
   ,ISNULL(B.CHARACTER_OCTET_LENGTH, 0)   AS ByteSize
   ,ISNULL(B.NUMERIC_PRECISION, 0)        AS NumericPrecision
   ,ISNULL(B.NUMERIC_PRECISION_RADIX, 0)  AS NumericPrecisionRadix
   ,ISNULL(B.NUMERIC_SCALE, 0)            AS NumericScale
   ,ISNULL(B.DATETIME_PRECISION, 0)       AS DateTimePrecision
   ,CASE B.IS_NULLABLE
        WHEN 'YES' THEN 'NULL'
        ELSE            ''
    END                                   AS [NULL]
   ,(
        SELECT
            CASE 
                WHEN COUNT(*) > 0 THEN 'Y'
                ELSE                   'N'
            END
        FROM  syscolumns AS C
        JOIN  sysobjects AS D ON D.id = C.id
        WHERE C.status = 128
        AND   D.xtype  = 'U'
        AND   D.name   = B.TABLE_NAME
        AND   C.name   = B.COLUMN_NAME
    )                                     AS IsIdentity
   ,ISNULL(B.COLUMN_DEFAULT, '')          AS [Default]
   ,ISNULL(B.CHARACTER_SET_CATALOG, '')   AS CharacterSetCatalog
   ,ISNULL(B.CHARACTER_SET_SCHEMA, '')    AS CharacterSetSchema
   ,ISNULL(B.CHARACTER_SET_NAME, '')      AS CharacterSetName
   ,ISNULL(B.COLLATION_CATALOG, '')       AS CollationCatalog
   ,ISNULL(B.COLLATION_NAME, '')          AS [CollationName]
   ,ISNULL(B.DOMAIN_CATALOG, '')          AS DomainCatalog
   ,ISNULL(B.DOMAIN_SCHEMA, '')           AS DomainSchema
   ,ISNULL(B.DOMAIN_NAME, '')             AS DomainName
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS A
JOIN INFORMATION_SCHEMA.COLUMNS                 AS B ON  A.TABLE_NAME  = B.TABLE_NAME
                                                     AND A.COLUMN_NAME = B.COLUMN_NAME
ORDER BY
    A.TABLE_NAME       ASC
   ,A.CONSTRAINT_NAME  ASC
   ,B.ORDINAL_POSITION ASC;
728x90
반응형
그리드형(광고전용)
Posted by icodebroker

댓글을 달아 주세요