SELECT column_name AS [Name],
IS_NULLABLE AS [Null?],
DATA_TYPE + COALESCE('(' + CASE
WHEN Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) = -1 THEN 'Max'
ELSE Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
END + ')','') AS [Type]
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'EMP_TABLE' 

SELECT column_name AS [Name],
IS_NULLABLE AS [Null?],
DATA_TYPE + COALESCE('(' + 
CASE 
WHEN DATA_TYPE IN ('char','varchar','nvarchar') 
THEN
    CASE
    WHEN Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) = -1 THEN 'Max'
    ELSE Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
    END 
WHEN DATA_TYPE IN ('numeric','decimal','float','real') 
THEN
    CAST(NUMERIC_PRECISION AS varchar(2)) + COALESCE(',' + CAST(NUMERIC_SCALE AS varchar(2)),'')
END
+ ')','') AS [Type]
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'EMP_TABLE'