July 18, 2013 at 12:59 am
Hi,
When i try the below sql.I'm getting proper result for Varchar datatype
field columns,BUT FOR NUMERIC AND DATE type coulmn im getting null in data_type column.Can you please check this,
SELECT column_name AS [Name],
IS_NULLABLE AS [Null?],
DATA_TYPE + '(' + 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'
Output coming as:
NAME Null? Type
id No NULL
CODE YES varchar(20)
date YES Null
Requiredoutput:
NAME Null? Type
id No numeric(38)
CODE YES varchar(20)
date YES date
Any one can please help me on this..
Regards
July 18, 2013 at 1:13 am
Because for INT and DECIMAL the value for CHARACTER_MAXIMUM_LENGTH is a NULL value. Adding a NULL value to a string will result in a NULL value.
Change the CASE statement to:
, DATA_TYPE
+ CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NULL
THEN ''
WHEN Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) = - 1
THEN '(Max)'
ELSE '(' + Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) + ')'
END AS [Type]
July 18, 2013 at 1:23 am
Hi,
I tried with this logic also,But this time it is showing datatype as numeric but it is not showing value(range.like numeric(38))Here we can refer numeric_precision to get that '38' as range value
DATA_TYPE
+ CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NULL
THEN ''
WHEN Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) = - 1
THEN '(Max)'
ELSE '(' + Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) + ')'
END AS [Type]
July 18, 2013 at 1:33 am
You can place this code as the first CASE statement:
WHEN DATA_TYPE = 'decimal'
THEN '(' + Cast(NUMERIC_PRECISION AS VARCHAR(5)) + ',' + Cast(NUMERIC_SCALE AS VARCHAR(5)) + ')'
You can expand the CASE statement the same way for all other different datatypes...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply