[Database] [MSSQL] 테이블의 상세 정보(컬럼, 설명, 속성, 기본값, 키, 외래키) 한번에 확인하기.

* A_Table의 모든 정보들을 확인하는 쿼리는 다음과 같다.
* 이 쿼리는 아래 블로그의 쿼리를 일부 수정한 것이다.
원본 : http://rocabilly.tistory.com/60

SELECT      A.Table_Name,
            CASE WHEN C.Value is not null THEN C.Value
                    ELSE '' END Table_Description,
            A.Column_Name,
            CASE WHEN B.value is not null THEN B.value
                    ELSE '' END Colum_Description,
            A.Data_Type,
            CASE WHEN isNULL(CAST(A.Character_Maximum_Length as VARCHAR),  
                    CAST(A.Numeric_Precision as VARCHAR) + ',' + CAST(A.Numeric_Scale as VARCHAR)) is null THEN ''
                    ELSE isNULL(CAST(A.Character_Maximum_Length as VARCHAR),  
                    CAST(A.Numeric_Precision as VARCHAR) + ',' + CAST(A.Numeric_Scale as VARCHAR)) END Column_Length,
            CASE WHEN A.Column_Default is not null THEN A.Column_Default
                    ELSE '' END Column_Default,
            CASE WHEN D.name is not null THEN 'PK'
                    ELSE '' END Primary_Key,
            CASE WHEN E.name is not null THEN 'FK'
                    ELSE '' END Foreign_Key,
            CASE WHEN A.Is_Nullable = 'YES' THEN 'Y'
                    ELSE 'N' END Is_Nullable
FROM        INFORMATION_SCHEMA.COLUMNS A  
LEFT JOIN   SYS.EXTENDED_PROPERTIES B  
ON          B.Major_Id      = OBJECT_ID(A.Table_Name)   
AND         B.Minor_Id      = A.Ordinal_Position  
LEFT JOIN   (  
                SELECT  OBJECT_ID(Objname) Table_Id,Value  
                FROM    ::FN_LISTEXTENDEDPROPERTY(NULL, 'schema','dbo','Table',NULL, NULL, NULL)  
                 ) C
ON           C.Table_id  = OBJECT_ID(A.Table_Name)
LEFT JOIN   (SELECT k1.name, k1.object_id, type, k2.TABLE_NAME, k2.COLUMN_NAME FROM sys.objects k1
                LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k2
                ON k1.name = k2.CONSTRAINT_NAME
                WHERE k1.type = 'PK'
                ) D
ON   D.TABLE_NAME = A.TABLE_NAME
AND   D.COLUMN_NAME = A.COLUMN_NAME
LEFT JOIN   (SELECT k1.name, k1.object_id, type, k2.TABLE_NAME, k2.COLUMN_NAME FROM sys.objects k1
                LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k2
                ON k1.name = k2.CONSTRAINT_NAME
                WHERE k1.type = 'F'
                ) E
ON   E.TABLE_NAME = A.TABLE_NAME
AND   E.COLUMN_NAME = A.COLUMN_NAME
WHERE        A.Table_Name    = 'PT_BookResend'
ORDER BY    A.Table_Name, A.Ordinal_Position, A.COLUMN_NAME