T-SQL: Convert datatime2 to datetime for all columns of type datetime2

Question

I've got a database full of datetime2 columns than needs to be moved to a SQL 2005 database. So, I need to convert all these datetime2(7) columns to datetime.

How can I go about doing this?

Right now I've managed to select the table name and column name for all columns with the datetime2 datatype like this:

SELECT t.name, c.name, i.DATA_TYPE
FROM sys.tables AS t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN information_schema.columns i ON i.TABLE_NAME = t.name AND i.COLUMN_NAME = c.name
WHERE i.data_type = 'datetime2'

I just don't know how to do the rest.

Answer 1

... then you iterate over your results with the CURSOR and dynamically run the DDL like:

ALTER TABLE myTable ALTER COLUMN myColumn datetime [NOT] NULL

so that you get something similar to this (not tested):

Edit: added null-ability check as well:

DECLARE @SQL AS NVARCHAR(1024)
DECLARE @TBL AS NVARCHAR(255)
DECLARE @COL AS NVARCHAR(255)
DECLARE @NUL AS BIT
DECLARE CUR CURSOR FAST_FORWARD FOR
    SELECT  t.name, c.name, c.is_nullable
    FROM    sys.tables AS t
    JOIN    sys.columns c ON t.object_id = c.object_id
    JOIN    information_schema.columns i ON i.TABLE_NAME = t.name AND i.COLUMN_NAME = c.name
    WHERE   i.data_type = 'datetime2'
    ORDER BY t.name, c.name

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

CLOSE CUR;
DEALLOCATE CUR;