테이블 정보확인

CREATE proc [dbo].[up_tableinfo]
@TABLE_NAME VARCHAR(30)
AS

SELECT       
    [col Name] = C.COLUMN_NAME
    , [Type & Length] = COLUMN_TYPE
    , [Default Value] = COLUMN_DEFAULT
    , [NOT NULL] = CASE WHEN C.IS_NULLABLE = 'NO' THEN 'Y' ELSE '' END
    , PK = ISNULL(K.PK, '')
    , FK = ISNULL(K.FK, '')
    , [Description] = D.COLUMN_DESC
FROM
(
    SELECT 
    TABLE_NAME
    , COLUMN_NAME
    , COLUMN_DEFAULT
    , IS_NULLABLE
    , COLUMN_TYPE = CASE WHEN DATA_TYPE IN ('VARCHAR', 'NVARCHAR', 'char', 'nchar') THEN
    DATA_TYPE + '(' + CONVERT(VARCHAR(10), ISNULL(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION)) + ')'
    WHEN DATA_TYPE IN ('DECIMAL') THEN
    DATA_TYPE + '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) + ', ' + CONVERT(VARCHAR(10), NUMERIC_SCALE) + ')'      
    ELSE DATA_TYPE END
    , ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TABLE_NAME
) AS C
LEFT JOIN -- 키정보
(
    SELECT COLUMN_NAME
    , PK = MAX(PK)
    , FK = MAX(FK)
    FROM
    (
        SELECT
        COLUMN_NAME
        , PK = CASE WHEN K.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'Y' END
        , FK = CASE WHEN K.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN 'Y' END
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS K
        INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS C ON K.CONSTRAINT_NAME = C.CONSTRAINT_NAME
        WHERE K.TABLE_NAME = @TABLE_NAME
    ) AS K
    GROUP BY COLUMN_NAME
) AS K ON C.COLUMN_NAME = K.COLUMN_NAME
LEFT JOIN -- Description 정보
(
    SELECT
    COLUMN_NM = COL_NAME(XP.MAJOR_ID, XP.MINOR_ID) 
    , COLUMN_DESC = CAST(XP.[VALUE] AS NVARCHAR(4000))
    FROM SYS.EXTENDED_PROPERTIES XP 
    WHERE XP.CLASS = 1
    AND  XP.MINOR_ID > 0
    AND XP.MAJOR_ID = OBJECT_ID(@TABLE_NAME) 
    AND XP.NAME IN (N'MS_DESCRIPTION') 
) AS D ON C.COLUMN_NAME = D.COLUMN_NM
ORDER BY C.ORDINAL_POSITION