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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -