SQL Server Management Studio SSMS Convert all columns from float to nvarchar

Question

sorry if this has been asked before in some form (I've been looking around for it but haven't found something that fits my question exactly). What I am trying to do is take all of the tables in my database and address all the columns in those tables. What I want to do is have my query automatically change all float columns to nvarchar.

When I import my data, SSMS seems to be randomly assigning my columns with numbers in them as floats or nvarchars. I am running some queries to unionize a lot of tables so I need the columns to have the same types.

Currently, what I have, is that if I manually change all the columns to have the same types, I can unionize everything and cast dates / make certain things uppercase and that all works, but I was wondering if there was a way at the start of my query to make all floats into nvarchar.

Thanks in advance!

Edit: I should add that there are multiple data types per table. For instance, I could be working with a group that has floats, datetimes, and nvarchars. I need to make sure all the datetimes stay in their format while only floats turn into nvarchar.

Edit2: Okay, so the goal of all this is to write a query that will look in a section of related tables (they come from excel, so they are the worksheets of given workbook). The excel version is 97-2003, so it only goes up to 65000 rows per worksheet. Because of this, I have in my database Tables names like the following:

A_1
A_2
A_3
B_1
B_2
C_1
C_2
C_3
C_4

Where each letter corresponds to a different workbook. The end goal is write a query that will basically say something like this: For all columns in Tables A_1 A_2 and A_3, if the column is float, make it varchar. After that, Union A_1 A_2 and A_3. After Unionization, UPPER(Header1), UPPER(Header2), CAST(Header3) AS DATE, etc.

I can already do most of this, but I don't want to manually go through every time and change every column that is type float to type nvarchar. I've already written the part of the query that does everything else.

Answer 1

Try this

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += 'alter table ' + TABLE_NAME + ' alter column '
               + COLUMN_NAME + ' nvarchar(50) '
FROM   INFORMATION_SCHEMA.columns
WHERE  DATA_TYPE = 'float'

--print @sql
EXEC Sp_executesql @sql