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...