Get the SQL table column schema with Datatype and Max Length in number value

Question

I have a Table_A with the column Name and Data Type as below:

QUANTITY int PO_NO varchar(13) FLG char(1) AMOUNT money

I want to make the query that return the result like this:

QUANTITY TYPE="3" LENGHT="4" PO_NO TYPE="200" LENGHT="13" FLG TYPE="129" LENGHT="1" AMOUNT TYPE="6" LENGTH="8"

I use this query to get column schema, but the result is not same as my expectation

select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Table_A'

Results:

COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH QUANTITY int NULL PO_NO varchar 13 FLG char 1 AMOUNT money NULL

So , my question is: How to know the data type int is equivalent to Type=3 and length =4 and so on.

SQL Server 2008. Please se my post at: stackoverflow.com/questions/49586370/… — Cát Tường Vy
– Cát Tường Vy, Commented Apr 1, 2018 at 9:04
The length in your desired result is not the table schema but the physical bytes SQL Server uses to store the data. ISO SQL INFORMATION_SCHEMA views return the logical schema, not the physical implementation. You can get the desired result using catalog views like sys.columns together with mapping SQL types to ADO types codes. Why do you care about the physical length in bytes of the data types? — Dan Guzman
– Dan Guzman, Commented Apr 1, 2018 at 12:15

ScaisEdge · Accepted Answer · 2018-04-01 09:27:03Z

1

do the fact that int, mother and other not string data type have fidex defined length you cant find a proper value in column CHARACTER_MAXIMUM_LENGTH if you need you could remap ths with a CASE WHEN

select COLUMN_NAME , DATA_TYPE , CASE DATA_TYPE WHEN 'int' THEN 4 WHEN 'money' THEN 8 ELSE CHARACTER_MAXIMUM_LENGTH END , CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Table_A'

edited Apr 1, 2018 at 9:27

answered Apr 1, 2018 at 9:16

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cát Tường Vy Over a year ago

But I need the CHARACTER_MAXIMUM_LENGTH too. I have not found any book about SQL Server that can explain to me why Data Type : money has Type=6 and Lenght=8 and so on. I want to understand this to make the XML file from the SQL table

Cát Tường Vy Over a year ago

I think I will use your solution with some modification to suit my project when export table to XML file , this file will be import into many kind of RDBMS

Collectives™ on Stack Overflow

Get the SQL table column schema with Datatype and Max Length in number value

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related