I have a database that has multiple tables with
datetime2 fields. I need to convert all of these to
datetime fields. I ran this syntax that produced over 300 tables that need to be altered with roughly 1200 fields.
select so.name table_name ,sc.name column_name ,st.name data_type from sysobjects so inner join syscolumns sc on (so.id = sc.id) inner join systypes st on (st.type = sc.type) where so.type = 'U' and st.name IN ('DATETIME2') ORDER BY so.name ASC
Now, for speed, I thought I would write some syntax to do this for me, but I must have set-up something incorrectly as I allowed this to run for 30 minutes and it was still running. If I run the print statements everything prints out as it should (at least to my semi-trained eye). Is > 30 minutes acceptable time for these alter table statements to complete or is something slowing me down/should I not use a cursor?
Declare @sql nvarchar(max), @tablename varchar(100), @fieldname varchar(100) Select table_name ,column_name FROM #Helper Order by table_name asc DECLARE cursor1 CURSOR FOR Select table_name ,column_name FROM #Helper ORDER BY table_name asc OPEN cursor1 FETCH NEXT FROM cursor1 INTO @tablename, @fieldname WHILE @@FETCH_STATUS = 0 BEGIN Set @SQL = 'Alter Table '+@tablename+' Alter Column '+@fieldname+' datetime' PRINT @SQL --EXEC sp_executesql @sql; FETCH NEXT FROM cursor1 INTO @tablename, @fieldname END CLOSE cursor1 DEALLOCATE cursor1 DROP TABLE #Helper
I let the statement run a bit longer and discovered that it is actually hitting an error with one of the constraints that (could or could not) be causing the process to take so long. I am trying to run this statement
Alter Table TestTable Alter Column datecreate datetime
And this error message is thrown:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Test_Table___datec__0D7A0286' is dependent on column 'datecreate'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN datecreate failed because one or more objects access this column.
Now if I
script constraint as - create to this is the output that is generated:
ALTER TABLE [dbo].[TestTable] ADD DEFAULT (getdate()) FOR [datecreate]
Nothing in there (to me) seems to be keeping this column as a
datetime2 data type. Do I need to add an extra check in my syntax to determine if the column has a constraint, and if it does, then drop the constraint and add it back?