up vote 0 down vote favorite

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?

share|improve this question
  • Why are you changing datetime2 to datetime? Datetime has less precision and requires more space than the equivalent datetime2(3). – Dan Guzman Dec 10 '16 at 19:53
  • Is this for a different version of SQL Server, or a different RDBMS? – Randolph West Dec 10 '16 at 19:57
  • It is a SQL Server backend with Access 2013 front-end. Access can not handle the datetime2 field type appropriately. – SmallFries BigGuys Dec 10 '16 at 20:03
  • Yes, you need to drop constraint first, then change the type of the column, then create constraint back. Default constraint is not the only thing that can prevent changing the column type. Foreign key constraints, indexes, views and other objects defines with schemabinding are the first that come to mind. – Vladimir Baranov Dec 11 '16 at 10:37
up vote 0 down vote

Changing datetime2 to datetime is a fundamental data type change. Each ALTER will update every row in the specified table. This can take a while depending on the size of your tables, especially if your largest tables have many datetime2 columns. Also, if there is any other concurrent activity, the DDL will be blocked by other activity.

share|improve this answer
  • no one else should have any current transactions (or any other activity) on this database at this time (why I am doing it over the weekend). Is there a way for me to "lock" the table before I begin the alter statement so that no other activity can take place other than my alter table statement? – SmallFries BigGuys Dec 10 '16 at 19:52
  • The ALTER will lock the table with a schema modification lock. However, it will need to wait for queries against the table to complete before the lock is granted. You could place the database is single or restricted user mode to make sure there is no other activity. Check for blocking to see if that's a problem. It may be taking a long time because your tables are large. – Dan Guzman Dec 10 '16 at 19:56
  • would it be more time efficient if I did something like this, or would that essentially take more time due to all the transactions that are occuring? Alter Table red Add d123 datetime Update red Set d123 = d2 ALTER TABLE red DROP COLUMN d2 EXEC sp_RENAME 'red.d123', 'd2', 'COLUMN' – SmallFries BigGuys Dec 10 '16 at 20:44
  • 1
    @SmallFriesBigGuys, the new column followed by update and drop will also need to update every row. A more efficient method (with no other activity) is to create new tables, migrate data with INSERT...SELECT, drop the old tables and rename. – Dan Guzman Dec 11 '16 at 12:15
  • I ended up going the route listed in your last comment with Creating new tables, insert...select statements. It still took some time, but was much easier than having to drop/re-create constraints after the datatype was altered. – SmallFries BigGuys Dec 11 '16 at 22:03
up vote 0 down vote

First execute:

ALTER TABLE TestTable DROP CONSTRAINT DF__Test_Table___datec__0D7A0286


Then execute:

Alter Table TestTable
Alter Column datecreate datetime
share|improve this answer
  • that would remove the constraint and allow me to alter the table, but how could I add the constraint back? Ideally, I would want a programmatic solution so that I am not manually dropping constraints and adding them back manually 1 by 1. – SmallFries BigGuys Dec 11 '16 at 22:02
  • I was thinking that you can: 1. use the same loop you are using 2. add an additional loop to traverse the constraints for each table that the cursor is on 3. store each constraint information (column name, expression, etc) into a temporary table 4. drop all constraints 5. change datecreate datatype 6. re-create the constraints using the temp table 7. move on to another table and repeat steps 1-6. The easier and less time consuming way is to follow what @Dan Guzman suggested. – Raidenlee Dec 12 '16 at 18:56

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.