Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL-Server
I used the query as
 
select COLUMN_NAME,DATA_TYPE from information_schema.columns where table_name = 'Dtype' order by ordinal_position
 
its output is

DATA_TYPE COLUMN_NAME
id int
type varchar
 

here the datatype is shown as varchar but i need like as varchar(50)
 
what i had select in the table creation time i want that so please help me i searched but i don't got.....
 

and also for decimal it gave as decimal instead of decimal(18,10),,,,....
Posted 18-Dec-12 23:03pm
Edited 20-Dec-12 19:07pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this one
 
SELECT
    COLUMN_NAME,
    (CASE WHEN CAST(DATA_TYPE AS VARCHAR) IN ('int', 'tinyint') THEN DATA_TYPE
         ELSE DATA_TYPE + ' (' + CONVERT(VARCHAR, ISNULL(CHARACTER_MAXIMUM_LENGTH,0)) + ')' END) AS DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Dtype'
ORDER BY ORDINAL_POSITION
 
You can keep on adding different types in the case clause, even you can also add a new WHEN in the CASE clause.
 
Hope it helps you...
 
Happy Coding... Smile | :)
 
Regards,
Vamsi
  Permalink  
Comments
Member 9644686 at 19-Dec-12 4:34am
   
Thanks a lot...
Menon Santosh at 20-Dec-12 8:03am
   
+5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

use this
select COLUMN_NAME,
    case when DATA_TYPE like '%var%' then Data_Type + '(' + case when character_Maximum_Length=-1 then 'Max' else convert(varchar,character_Maximum_Length) end  + ')'
    else
        case when DATA_TYPE like '%num%' then  Data_Type + '(' + convert(varchar,numeric_Precision) + ',' +  convert(varchar,numeric_Scale)   + ')'
            else Data_type
        end
    end as datatype
from information_schema.columns
where table_name = 'DType' order by ordinal_position
Happy Coding!
Smile | :)
  Permalink  
Comments
Member 9644686 at 19-Dec-12 4:34am
   
Thanks a lot...
Aarti Meswania at 19-Dec-12 4:52am
   
welcome! :)
Glad to help you! :)
 
do u have give us stars(vote answer)? :)
Menon Santosh at 20-Dec-12 8:03am
   
+5
Aarti Meswania at 20-Dec-12 8:12am
   
Thank you!
:)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Here is the working SQl for your requirement
 
SELECT column_name
    , CASE
        WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN
           DATA_TYPE + '(' + convert(varchar(MAX), CHARACTER_MAXIMUM_LENGTH) + ')'
        WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN
           DATA_TYPE
       END as DATA_TYPE
FROM
    information_schema.columns
WHERE
    table_name = 'TABLENAME'
ORDER BY
    ordinal_position
  Permalink  
Comments
Member 9644686 at 19-Dec-12 4:34am
   
Thanks a lot...
Menon Santosh at 20-Dec-12 8:04am
   
+5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 304
1 Sergey Alexandrovich Kryukov 295
2 Shweta N Mishra 216
3 Maciej Los 210
4 PIEBALDconsult 184
0 OriginalGriff 7,630
1 Sergey Alexandrovich Kryukov 7,022
2 DamithSL 5,586
3 Manas Bhardwaj 4,946
4 Maciej Los 4,525


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 21 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100