Change All Columns Data Type in SQL Server

Question

When I imported the table into SQL, it suggested a real data type, now I would like to change all columns to double type ...

Is there any script to automatically execute this in SQL Managment Studio

My table is 500 columns:

`After doing` EXECUTE sp_help traS Col Type Comp len Prec Scale Nullable TrimTrailing Fixed Collation ------------------------------------------------------------------------------- x1 real no 4 24 NULL yes (n/a) (n/a) NULL x2 real no 4 24 NULL yes (n/a) (n/a) NULL x3 real no 4 24 NULL yes (n/a) (n/a) NULL x4 real no 4 24 NULL yes (n/a) (n/a) NULL ... x500 real no 4 24 NULL yes (n/a) (n/a) NULL 

Answer 1

The following code will put a list of columns in a temporary table named @cols , loop through that table, generate an alter table alter column statement and execute it for each column.

If you need to exclude columns, you must include them in the NOT IN predicate to select from information_schemes.columns.

 declare @cols table (i int identity, colname varchar(100)) insert into @cols select column_name from information_schema.COLUMNS where TABLE_NAME = 'yourtable' and COLUMN_NAME not in ('exclude1', 'exclude2') declare @i int, @maxi int select @i = 1, @maxi = MAX(i) from @cols declare @sql nvarchar(max) while(@i <= @maxi) begin select @sql = 'alter table yourtable alter column ' + colname + ' decimal(18,4) NULL' from @cols where i = @i exec sp_executesql @sql select @i = @i + 1 end 

Answer 2

A rough psuedocode will look like this. However, it has not been tested since I do not have a virtual virtual machine

 -- Create a cursor that will iterate through -- all the rows that meet the criteria DECLARE csr CURSOR FOR -- This query attempts to define the set of columns -- that are reals SELECT SC.name AS column_name FROM sys.tables ST INNER JOIN sys.columns SC ON SC.object_id = ST.object_id INNER JOIN sys.types T -- these column names are close but not right ON T.type_id = SC.system_type_id WHERE -- make this your table name ST.name = 'traS' -- look at actual values in sys.types AND T.name = 'real' DECLARE -- this holds the current column name @column_name sysname , @base_query varchar(max) , @actual_query varchar(max) -- template query for fixing what buggered SET @base_query = 'ALTER TABLE traS ALTER COLUMN [<X/>] decimal(18,2) NULL' FETCH NEXT FROM csr INTO @column_name WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN BEGIN TRY SET @actual_query = REPLACE(@base_query, '<X/>', @column_name) EXECUTE (@actual_query) END TRY BEGIN CATCH PRINT 'Failed executing statement ' PRINT @actual_query END CATCH END FETCH NEXT FROM csr INTO @colum_name END CLOSE csr DEALLOCATE csr 

The orange bar above my head says I'm too slow, but I will send anyway, since I spent too much time typing;)