sql - How to compare datatype of column to a given datatype -

i need create sql change script checks specific datatype of column , edits datatype if column of type.

for example

if (select data_type information_schema.columns      table_name = 'table' , column_name = 'xml') = 'xml') 

i want above code return true if 'xml' column in 'table' of type xml. need change varchar(max).

for such scripts use sql server metadata/catalog views instead of views information_schema:

for untyped xml columns:

if exists(     select  *        sys.columns c     join    sys.tables t on c.object_id = t.object_id     join    sys.schemas s on t.schema_id = s.schema_id     join    sys.types tp on c.user_type_id = tp.user_type_id       s.name = n'dbo'     ,     t.name = n'table1'     ,     c.name = n'col2'     ,     tp.name= n'xml' ) begin     alter table dbo.table1     alter column ... end 

for typed xml columns:

if exists(     select  xmlcol.*        sys.columns c     join    sys.tables t on c.object_id = t.object_id     join    sys.schemas s on t.schema_id = s.schema_id     join    sys.xml_schema_collections xmlcol on c.xml_collection_id = xmlcol.xml_collection_id     join    sys.schemas xmlcols on xmlcol.schema_id = s.schema_id       s.name = n'production'     ,     t.name = n'productmodel'     ,     c.name = n'instructions'     ,     xmlcols.name = n'production'     ,     xmlcol.name = n'manuinstructionsschemacollection' ) begin     alter table dbo.table1     alter column ... end 

note: if convert xml values text values use nvarchar instead of varchar.