Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get correct datatype names for numeric and date type values Expand / Collapse
Author
Message
Posted Thursday, July 18, 2013 12:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:52 AM
Points: 3, Visits: 5
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

Post #1474913
Posted Thursday, July 18, 2013 1:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:17 PM
Points: 2,305, Visits: 2,783
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]



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1474917
Posted Thursday, July 18, 2013 1:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:52 AM
Points: 3, Visits: 5
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]
Post #1474919
Posted Thursday, July 18, 2013 1:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:17 PM
Points: 2,305, Visits: 2,783
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...


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1474920
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse