4

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Mel
  • 53
  • 1
  • 4

5 Answers5

6

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)
devio
  • 36,858
  • 7
  • 80
  • 143
  • The only thing I'd change is using `sys.tables` instead of `sys.objects` to find the table name – marc_s Sep 05 '10 at 08:31
2

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;
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Great answer, gets my vote. I would always wrap such code in a transaction though, just in case something goes wrong. Also note the above script will fail if you have constraints on the column (eg. like a default that inserts getdate()). – rmcsharry Jul 04 '12 at 05:04
  • But you can solve that problem by dropping them (http://stackoverflow.com/questions/4215619/how-to-drop-all-default-constraints-in-a-database) and then adding them again afterwards. – rmcsharry Jul 04 '12 at 05:24
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. :)

Dan J
  • 16,319
  • 7
  • 50
  • 82
0

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]
davisoa
  • 5,407
  • 1
  • 28
  • 34
0

You can do something like this:

SELECT
 'ALTER TABLE [' + Table_Schema+'].['+Table_Name
 +'] Alter Column ['+Column_Name+'] datetime2;'
 FROM
 INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE='datetime';

Now you have all the scripts necessary to make your bulk type change.

Ref: https://www.sqlservercentral.com/forums/topic/how-to-change-column-type-on-all-tables-of-a-certain-database

kiafiore
  • 1,071
  • 2
  • 11
  • 27