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?
alter table <table> alter column <column> nvarchar(max) [not] null-- change as per you want to allow null or not.