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.
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 datatypefield 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.ColumnsWHERE table_name = 'EMP_TABLE' Output coming as:NAME Null? Typeid No NULLCODE YES varchar(20)date YES NullRequiredoutput:NAME Null? Typeid No numeric(38)CODE YES varchar(20)date YES dateAny 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 NULLSELECT column_name AS [Name],IS_NULLABLE AS [Null?],DATA_TYPE + COALESCE('(' + CASEWHEN Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) = -1 THEN 'Max'ELSE Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))END + ')','') AS [Type]FROM INFORMATION_SCHEMA.ColumnsWHERE table_name = 'EMP_TABLE' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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? Typeid No NUMERICCODE YES varchar(20)date YES DATEbut my req output is like this,NAME Null? Typeid No numeric(38)CODE YES varchar(20)date YES dateIt 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.ColumnsWHERE table_name = 'EMP_TABLE' [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
|