Database Administration
sql-server sql-server-2012 table dependencies drop-table
Updated Sat, 21 May 2022 08:48:55 GMT

How to safely recreate a MS SQL table that has other objects depending on it?


I have a table in A Microsoft SQL 2012 database, called Courses. It was "upconverted" from an originally SQL 2000 database, upconverted to SQL 2008 database, and then upconverted again to 2012 (You can't directly upconvert from 2000 to 2012).

A result of this is that we have some tables that have the old text/ntext datatype, and I want to change the datatype to varchar(max) or nvarchar(max). For reasons I don't quite understand, I can't simply change the datatype of the ntext columns to nvarchar(max), because whenever I try to do that in Table Design view, I get this error:

enter image description here

Additionally, If I try to simply right click the Courses table in Management studio and Delete it, I see that there are quite a few other database objects dependent on Courses, and upon attempted delete, I get an error message saying I can't do so, because Courses is referenced by another Foreign Key constraint from another object.

So, essentially what I want to do is recreate a table, that has other objects depending on it, and the only thing different about the recreation is that I want to change the text/ntext columns in it to varchar(max) and nvarchar(max) datatypes. Everything currently "depending" on the 'old' Courses table, needs to then "depend" on the 'new' recreated Courses table. What's the easiest way to go about doing so in SQL 2012?




Solution

The simple way around this is to change some options in Management Studio.

Go to Tools, Options, Designers, Table and Database Designers. Uncheck "Prevent saving changes that require table re-creation".

Generally speaking, Management Studio will properly handle dependencies when you modify a table in a way that requires it to be recreated (it will do assorted tricks with temp tables behind the scenes, which you can see if you tell it to generate a change script instead of actually making the changes). However, this may involve modifications/changes to related tables or foreign keys. In other words, don't do this on a live system if you can help it, and make sure you've got backups before you do it.





Comments (5)

  • +0 – I'd be so scared of making the change like that. Good advice to take a backup first; I'd add "test the backup" to that! — Feb 07, 2017 at 16:46  
  • +1 – @MaxVernon Yes, an excellent bit of advice. You don't need a backup plan, you need a restore plan! — Feb 07, 2017 at 16:48  
  • +1 – Instead of letting Management Studio apply the change (which could timeout in case of a very large table) you could generate the script instead. Examine the script, modify it to suit your needs (remove the go statements, put the whole thing in a transaction, etc.) Always be very careful and TEST TEST TEST — Feb 07, 2017 at 17:20  
  • +1 – @datagod Yup, generating the script is a good way to go. However, I've seen the generated script fail in ways that can't be cleanly rolled back, which I've not seen happen with the object designer saving the changes, so be extra cautious. There's presumably something a little different happening with regards to transactions, XACT_ABORT, etc. — Feb 07, 2017 at 17:27  
  • +0 – DB2, thank you so much for this suggestion. It seems to work in my test instance - I've been able to change the datatypes in Management studio with the disabling of that setting! Will make a backup before doing this in production. — Feb 08, 2017 at 15:32  


External Links

External links referenced by this document: