Query full column datatypes MSSQL

Question

I need to query full columns datatype, eventually with length specification.

If I use INFORMATION_SCHEMA.COLUMNS or sp_Columns(@tablename) I get type, size, and digits in different columns and I have to decode each datatype with a case statement.

I wrote a query like that, but it's not much solid and i'm not sure is always right. I hope there is a way to retrive all column type in one column.

Here's my query, and I still have to decode many types:

select
    a.TABLE_SCHEMA, 
    a.TABLE_NAME, 
    COLUMN_NAME, 
    case 
        when CHARACTER_MAXIMUM_LENGTH is not null then DATA_TYPE+'('+convert(varchar, CHARACTER_MAXIMUM_LENGTH)+')'
        when DATA_TYPE in ('int','datetime','time','date','text') then DATA_TYPE
        when DATA_TYPE in ('decimal','numeric') then DATA_TYPE+'('+convert(varchar,NUMERIC_PRECISION) +','+ convert(varchar,NUMERIC_SCALE) +')'
        else '' 
        end as FULL_DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS a
inner join INFORMATION_SCHEMA.TABLES b
    on b.TABLE_NAME = a.TABLE_NAME
    and b.TABLE_SCHEMA = a.TABLE_SCHEMA
    and b.TABLE_TYPE = 'BASE TABLE'

score 0 · Answer 1 · answered Mar 10 '15 at 13:54

Find the below query if the result come would be you satisfactory or it can help you to come close to the result

select
t.object_id as 'Table ID',
object_name(t.object_id) as 'Table Name',
c.column_id 'Columns ID',
c.name as 'Column Name',
c.system_type_id as 'Data Type ID',
ty.name + '(' + convert(varchar(10),c.max_length) + ')'as 'Data Type'

from
sys.tables t inner join sys.columns c
on t.object_id=c.object_id
left join sys.types ty
on c.system_type_id =ty.user_type_id

No, because I get datas like `int(4)` or `decimal(13)`. In SQLServer `declare @V int(4)` is syntax error, and decimal(13) do not retrive digits of precision. — Radioleao, Mar 10 '15 at 14:46

Query full column datatypes MSSQL

1 Answers1