CODEWITHSUNDEEP
sqlsql-serversql-server-2008schema
Cát Tường Vy
Cát Tường Vy

Reputation: 398

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

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.

Upvotes: 1

Views: 2960

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

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'

Upvotes: 1

Related Questions