SQL Server 2008: Modifying a Mass Dataset

Question

I have a SQL Server 2008 database with many tables. I am using the lame datetime type now 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 somewhere to make a massive change from datatime to datetime2 ?

Answer 1

Run this in Management Studio, copy the result and paste it into a 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 2

Changing the data type usually requires ALTER TABLE statements:

 ALTER TABLE myTable ALTER COLUMN timestamp datetime2 [NOT] NULL 

To change all datetime columns to datetime2 in a given database and 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; 

Answer 3

This will be a little brute force method, but you can always find all the columns of the datetime data type using the sys.columns , grab the table name and column name, sys.columns over the list with the cursor, and create an ALTER TABLE statement for each record as follows:

 ALTER TABLE @tablename ALTER COLUMN @columnname datetime2 

Then run the specified statement using EXEC . Obviously, you need to have permissions for both the sys.columns query and the ALTER all these tables ...

Sorry that there is not much code in this answer - there is no copy of SSMS on this computer and cannot remember the syntax for all of this from memory. :)

Answer 4

I would use a query window and display all the ALTER TABLE statements that you need to execute. After all of them are generated, you can run the result in the database.

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

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