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:
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?
Best Answer
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.