Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET SQL Server
i used this query for retrieving column name and datatype
 

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#39;
ORDER BY
    ordinal_position
 

for this query i got the varchar(50) but i got the decimal instead of decimal(18,10)
  and numeric instead of numeric(25,12) like this i need this so please help me
Posted 20-Dec-12 19:17pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi..
 
Try this following code block
 
SELECT C.Name ColumnName,S.Name + CASE WHEN C.precision > 0 and C.scale > 0
THEN '('+CAST(C.precision AS varchar) +','+ CAST(C.scale As varchar) +')'
ELSE '('+CAST(C.max_length AS varchar) +')' END DatType FROM Sys.columns C JOIN 
Sys.types S ON C.system_type_id = S.system_type_id JOIN Sys.tables T
ON C.object_id = T.object_id WHERE T.Name Like 'YourTableName'
 
Note-: Replace 'YourTableName' with your table name.
 

Thank you
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 240
1 Kamal Rocks 184
2 PIEBALDconsult 150
3 BillWoodruff 148
4 Jochen Arndt 135
0 OriginalGriff 5,695
1 DamithSL 4,506
2 Maciej Los 4,007
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web03 | 2.8.141216.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