2

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?

2
  • 1
    Have you considered changing the column data types (or creating new columns with the required data types) instead of dropping the entire table?
    – mustaccio
    Feb 7, 2017 at 16:44
  • 2
    I would just change the datatype with tsql alter table <table> alter column <column> nvarchar(max) [not] null -- change as per you want to allow null or not.
    – Kin Shah
    Feb 7, 2017 at 16:52

3 Answers 3

3

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.

5
  • 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!
    – Hannah Vernon
    Feb 7, 2017 at 16:46
  • 1
    @MaxVernon Yes, an excellent bit of advice. You don't need a backup plan, you need a restore plan!
    – db2
    Feb 7, 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
    – datagod
    Feb 7, 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.
    – db2
    Feb 7, 2017 at 17:27
  • 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.
    – Rob V
    Feb 8, 2017 at 15:32
2

You can modify the ntext columns to nvarchar(max) using T-SQL. I'd only recommend doing this if the table/database is not actively being used. If the table is very small, this method will be quite fast and typically problem-free. However, if you have hundreds of millions of rows or more, I'd be concerned about doing this without testing it first in a non-production environment so you have some idea about how it works, and what space requirements will be in your situation.

To show how this works, and to prove it can be done, I've setup this test-bed.

First, we create a table with an ntext column, and populate it with 1,000 rows of random data:

USE tempdb;

CREATE TABLE dbo.OldTypes
(
    MyNTextColumn ntext NOT NULL
);

;WITH cte AS (
    SELECT v.Num
    FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.OldTypes (MyNTextColumn)
SELECT CONVERT(ntext, CONVERT(nvarchar(4000), CRYPT_GEN_RANDOM(8000)))
FROM cte c1
    CROSS JOIN cte c2
    CROSS JOIN cte c3;

Here, we convert the MyNTextColumn to nvarchar(max):

ALTER TABLE dbo.OldTypes
ALTER COLUMN MyNTextColumn nvarchar(max);

This drops the temporary table created above, to clean up:

DROP TABLE dbo.OldTypes;
4
  • Thank you Max. The table is fairly small, and this: ALTER TABLE dbo.OldTypes ALTER COLUMN MyNTextColumn nvarchar(max); does not work since I get an error about dependencies. But, this information in conjunction with DB2's answer, has allowed me to make the changes via Management Studio. Thanks.
    – Rob V
    Feb 8, 2017 at 15:30
  • I'd be curious to see what dependencies you're talking about. Any chance you can add the DDL for some of the objects involved?
    – Hannah Vernon
    Feb 8, 2017 at 17:33
  • 1
    I'm really amateur, so your request is going a bit over my head, but I'm viewing the dependencies in this screen: imgur.com/a/ofLVq Some of them are tables, some are views.
    – Rob V
    Feb 9, 2017 at 15:35
  • ahhh, I see. Schema binding might make a simple conversion like the one I mentioned a bit more difficult. You'd need to remove the schema binding from each schema-bound view that references the table containing the ntext columns, change the ntext column to nvarchar(max), then re-schema-bind the views.
    – Hannah Vernon
    Feb 9, 2017 at 18:09
0

Alternative way:

Perform a backup before continuing.

You could...

  1. add a new column transfer_temp nvarchar(max) to your existing table
  2. move the data to the new column
  3. rename the column <old_column_name> to <old_column_name>_back
  4. rename the new column transfer_temp to <old_column_name>
  5. test the data
  6. Drop the old column <old_column_name>_back

The advantage is that your data is still in the old column definition up until you drop the column after testing the application and/or data.

This can be achieved as follows:

ALTER TABLE <your_table> ADD transfer_temp AS nvarchar(max)

Then move the data from your old column to the new column:

UPDATE <your_table> SET transfer_temp = <old_column_name>

Rename your columns:

EXEC sp_rename 'dbo.<your_table>.<old_column_name>', '<old_column_name>_back', 'COLUMN'
GO
EXEC sp_rename 'dbo.<your_table>.transfer_temp', '<old_column_name>', 'COLUMN'

Test your application again.

Drop the old data:

ALTER TABLE <your_table> DROP COLUMN <old_column_name>_back

Finished.

sp_rename

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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