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
EDIT
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?