Click here to Skip to main content
15,937,003 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i used this query for retrieving column name and datatype


SQL
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

1 solution

Hi..

Try this following code block

SQL
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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900