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)



Advertise | Privacy | Mobile
Web02 | 2.8.150302.1 | Last Updated 21 Dec 2012
Copyright © CodeProject, 1999-2015
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