SQL Server 2008: Bulk Datatype Change

Question

I have an SQL Server 2008 database with many tables. I've been using the now lame datetime datatype and want to use the new and better datetime2. In most places where I have a datetime field, the corresponding column name is Timestamp. Is there anywhere to do a bulk change from datatime to datetime2?

Answer 1

This would be a bit of a brute-force method, but you could always look up all columns of datatype datetime using the sys.columns view, grab the table name and column name, iterate over that list with a cursor, and for each entry generate an ALTER TABLE statement like so:

ALTER TABLE @tablename ALTER COLUMN @columnname datetime2

Then run said statement with EXEC. Obviously, you'd need to have permissions both to query sys.columns and to ALTER all of those tables...

Apologies there isn't more code in this answer - don't have a copy of SSMS on this machine, and can't remember the syntax for all of that from memory. :)

Answer 2

I would use a query window, and output all of the ALTER TABLE statements you need to perform this. Once you have them all generated, you can run the result against the database.

if you select from SYSCOLUMNS the names of the tables and fields that you want, you can generate the statements you need to change all of the columns in the database to datetime2.

ALTER TABLE {tablename} ALTER COLUMN {fieldname} datetime2 [NULL | NOT NULL]

Answer 3

Run this in Management Studio, copy the result and paste into new Query Window:

select 'ALTER TABLE ' + OBJECT_NAME(o.object_id) + 
    ' ALTER COLUMN ' + c.name + ' DATETIME2 ' +
    CASE WHEN c.is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END 
from sys.objects o
inner join sys.columns c on o.object_id = c.object_id
inner join sys.types t on c.system_type_id = t.system_type_id
where o.type='U'
and c.name = 'Timestamp'
and t.name = 'datetime'
order by OBJECT_NAME(o.object_id)

Answer 4

Data type alteration generally requires ALTER TABLE statements:

ALTER TABLE myTable ALTER COLUMN timestamp datetime2 [NOT] NULL

To change all the datetime columns into datetime2 in a given database & schema:

DECLARE @SQL AS NVARCHAR(4000)
DECLARE @table_name AS NVARCHAR(255)
DECLARE @column_name AS NVARCHAR(255)
DECLARE @isnullable AS BIT

DECLARE CUR CURSOR FAST_FORWARD FOR
    SELECT c.table_name, 
           c.column_name, 
           CASE WHEN c.is_nullable = 'YES' THEN 1 ELSE 0 END AS is_nullable
      FROM INFORMATION_SCHEMA.COLUMNS c 
     WHERE c.data_type = 'datetime'
       AND c.table_catalog = 'your_database'
       AND c.table_schema = 'your_schema'
    -- AND c.table_name = 'your_table'

OPEN CUR
FETCH NEXT FROM CUR INTO @table_name, @column_name, @isnullable
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @SQL = 'ALTER TABLE ' + @table_name + ' ALTER COLUMN ' + @column_name + ' datetime2' + (CASE WHEN @isnullable = 1 THEN '' ELSE ' NOT' END) + ' NULL;'
    EXEC sp_executesql @SQL
    FETCH NEXT FROM CUR INTO @table_name, @column_name, @isnullable
END

CLOSE CUR;
DEALLOCATE CUR;