類似Oracle的desc指令

CREATE PROCEDURE up_desc (
    @TABLE_NAME NVARCHAR(128)  -- 資料表名稱
)AS
SELECT COLUMN_NAME,
CASE DATA_TYPE
        WHEN 'char' THEN DATA_TYPE+'('+CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH)+')'
        WHEN 'varchar' THEN DATA_TYPE+'('+CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH)+')'
        WHEN 'nvarchar' THEN DATA_TYPE+'('+CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH)+')'
        WHEN 'numeric' THEN DATA_TYPE+'('+CONVERT(VARCHAR(10),NUMERIC_PRECISION)+','+CONVERT(VARCHAR(10),NUMERIC_SCALE)+')'
        ELSE DATA_TYPE END AS OBJECT_DATA_TYPE,
    B.value as [OBJECT_DESCRIPTION]
FROM [INFORMATION_SCHEMA].[COLUMNS] A
  LEFT JOIN ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'TABLE', @TABLE_NAME, 'COLUMN', default) B ON A.COLUMN_NAME=B.objname
WHERE TABLE_NAME=@TABLE_NAME
ORDER BY A.ORDINAL_POSITION