Please help me on Numeric and date datatype logic

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help me on Numeric and date datatype logic

Author

Topic

aniletc37
Starting Member

4 Posts

Posted - 2013-07-18 : 02:54:14

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 02:59:51

thats because all other datatypes have max length value as NULL


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'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

aniletc37
Starting Member

4 Posts

Posted - 2013-07-18 : 03:16:49

Hi Vishak,

Thx for reply.But your code giving result like this,

NAME Null? Type
id No NUMERIC
CODE YES varchar(20)
date YES DATE

but my req output is like this,
NAME Null? Type
id No numeric(38)
CODE YES varchar(20)
date YES date

It is not showing values(range) for Numeric and decimal columns.

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 04:24:26

[code]
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'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources