SQL for converting ALL Int16 FieldTypes to Float for a given table in a SQL Server

Question

We have an application that creates Tables using Int16 for numeric fields. However we need these field types to be Float.

What SQL statement can user to ALTER all Int16 Fields to change them Float?

Answer 1

You can try to generate a command and execute it after. You can do something like this:

select 'ALTER TABLE ' + t.name + ' ALTER COLUMN ' + c.name + ' FLOAT = NULL'
from    sysobjects t join syscolumns c on c.id = t.id
where   t.xtype = 'U' AND C.xtype = 56

Run above select query, you will receive all tables individual alter table syntax.