Single SQL Query to update datatypes of all column

问题:

I have a SQL Server table which has 625 columns created with different datatypes like int, varchar(25), decimal(18, 2), etc...

Now am interested in changing datatype of all columns to varchar(255). Instead of executing below query for all the columns one by one, is there a single SQL Server query to change datatypes of all columns in a table at one shot?

ALTER TABLE dbo.Employee 
ALTER COLUMN FirstName VARCHAR(255) NOT NULL

Looking forward for your response.

回答1:

There is no one single "magic" bullet to do this - it's an operation that's rather unusual, so it's not supported natively.

What you can do is iterate over the columns of your table from the system catalog views, create such an ALTER statement on the fly, and also execute it - something like this:

DECLARE AlterTableCursor CURSOR FAST_FORWARD 
FOR
    SELECT 
        AlterCmd = 'ALTER TABLE dbo.YourTableNameHere ALTER COLUMN ' + name + ' VARCHAR(255) NULL'
    FROM 
        sys.columns
    WHERE 
        object_id = OBJECT_ID('dbo.YourTableNameHere')

DECLARE @AlterTableCmd NVARCHAR(200)

OPEN AlterTableCursor

FETCH NEXT FROM AlterTableCursor INTO @AlterTableCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@AlterTableCmd)

    FETCH NEXT FROM AlterTableCursor INTO @AlterTableCmd
END

CLOSE AlterTableCursor
DEALLOCATE AlterTableCursor

Replace the YourTableNameHere with your actual table name - and you should be good to go! Test this on a copy of your live data first !

回答2:

is not possible. You will need to do this one by one. You can do following things -

1. You can create a Temporary Table with your desired columns and type.
2. copy the data from original table to temporary table.
3. delete original table
4. Rename you Temporary Table to your original name

回答3:

I would say that it's not a good design to have 625 columns in one table, but you do what ever you have been asked to do.

You could generate a SQL string that contains multiple ALTER statements and execute it with EXEC. You have to be careful with the data conversion though. This is why it's probably better to do it in a cursor, because you can catch possible exceptions for the columns that cannot be altered. Here is a simple code which does it in two steps:

• generate the SQL string;
• execute it using EXEC

Code sample:

SELECT * FROM sys.columns WHERE OBJECT_NAME(object_id) = 'Employee'

DECLARE @SQL VARCHAR(MAX)
SET @SQL = (
SELECT '
ALTER TABLE dbo.Employee ALTER COLUMN ' + c.name + ' VARCHAR(255) NULL;
'
FROM sys.columns c WHERE OBJECT_NAME(object_id) = 'Employee'
FOR XML PATH(''), TYPE
).value('text()[1]', 'VARCHAR(MAX)')

PRINT @SQL

EXEC(@SQL)

SELECT * FROM sys.columns WHERE OBJECT_NAME(object_id) = 'Employee'