SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Please help me on Numeric and date datatype logic
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aniletc37
Starting Member

India
4 Posts

Posted - 07/18/2013 :  02:54:14  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/18/2013 :  02:59:51  Show Profile  Reply with Quote
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

India
4 Posts

Posted - 07/18/2013 :  03:16:49  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/18/2013 :  04:24:26  Show Profile  Reply with Quote

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' 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000