asked on

Change column data type from ntext to nvarchar

I need to update a large number of databases on the same SQL 2000 server. All databases have the same table and column structure and all we need to do is alter the column type.

Because SQL can't just convert NTEXT to NVARCHAR without recreating a table, a simple ALTER TABLE ALTER COLUMN command does not work.

If done manually through the Enterprise Mgr, the script is generated where a temporary table is created, data transferred to the new table, the old table is dropped and the Tmp table is then renamed to the original table name. Sample is attached (I modified it for security reasons).

Can someone tell me how I can run this or similar script that will run this update on all databases on the server as long as the following conditions are true:
1. The table and the column with such name exists.
2. The present column data type is NTEXT - this way if the column already NVARCHAR we could just skip over it.

I would prefer very specific instructions if possible as I am not that familiar with anything other than VBScript.

/*
 
   Tuesday, March 31, 2009 12:12:56 AM
 
   User: dbuser
 
   Server: (LOCAL)
 
   Database: testdb
 
   Application: MS SQLEM - Data Tools
 
*/
 
 
 
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_tblTest
	(
	idTest int NOT NULL IDENTITY (1, 1),
	testCode nvarchar(50) NULL,
	testStatus nvarchar(5) NULL,
	testResult nvarchar(10) NULL,
	testNotes nvarchar(4000) NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_tblTest ON
GO
IF EXISTS(SELECT * FROM dbo.tblTest)
	 EXEC('INSERT INTO dbo.Tmp_tblTest (idTest, testCode, testStatus, testResult)
		SELECT idTest, testCode, testStatus, testResult, CONVERT(nvarchar(4000), testNotes) FROM dbo.tblTest TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_tblTest OFF
GO
DROP TABLE dbo.tblTest
GO
EXECUTE sp_rename N'dbo.Tmp_tblTest', N'tblTest', 'OBJECT'
GO
GRANT REFERENCES ON dbo.tblTest TO dbuser  AS dbo
GRANT SELECT ON dbo.tblTest TO dbuser  AS dbo
GRANT UPDATE ON dbo.tblTest TO dbuser  AS dbo
GRANT INSERT ON dbo.tblTest TO dbuser  AS dbo
GRANT DELETE ON dbo.tblTest TO dbuser  AS dbo
COMMIT

Open in new window

Dimitris

'Add a new column
ALTER TABLE Tmp_tblTest ADD testNotes_Nvarchar nvarchar(4000)

'Move the values
UPDATE Tmp_tblTest
SET testNotes_Nvarchar = CONVERT(nvarchar(4000),testNotes)

'Drop Column testNotes
ALTER TABLE Tmp_tblTest DROP COLUMN testNotes

'Rename the new column to testNotes
EXEC sp_rename 'Tmp_tblTest.[testNotes_Nvarchar]', 'testNotes', 'COLUMN'

aspdevguy

ASKER

So what you are saying is that instead of recreating the entire table, I can just create a new column with the desired data type, move all the data into it and then drop the unneeded column and rename the new column into the original one, correct?

But how can I run this on multiple databases? Because that was kind of my question.

I can see how this can easily be written in ASP/VBScript. So should I just query the masterdb for all the DB names and loop through that list updating every database that meets the criteria? What are your thoughts on this?

Thanks!

aspdevguy

ASKER

Clarification & question:
By masterdb I meant the sysdatabases table.

Now that I think about it, I don't think executing this through ASP is the most efficient thing to do. Where on the SQL server can execute this script to update all the DBs?

Dimitris

So you need to update all the DBs
and all the tables that have an ntext column to nvarchar?

ASKER CERTIFIED SOLUTION

Dimitris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dimitris

P.S. IT WILL NOT EXECUTE THE SCRIPTS!
You can check them and run them when u want

aspdevguy

ASKER

OK... sorry for being so ignorant about this... but could you tell me where exactly I should plug this in and what, if any, messages I should be looking for (i.e. Executed successfully, etc.).

Thanks!

Dimitris

Ok...
You will run it in master DB

this script will read all the user database (line 16)
on your SQL server and will locate all the text and ntext (line 22 to 27) columns in these databases.

Then it will print on the query analyzer the statements that you have to run in order to change the columns from text or ntext to nvarchar(4000)

You will copy that output and you can run it whenever u like
If you change the line 49 and instead of PRINT you use the EXECUTE(@tmpSQL) then it will automatically execute the change from text to nvarchar. I Don't recommended because i think that it's better to check it first to see if it tries to update a column that you want to continue to be ntext or text

If you wan to run it in specific databases then you have to add a where on line 16 (example WHERE name='MyDB')
If you want to include only ntext and not text columns then you have to remove on line 27 the "' + char(39) + 'ntext' + char(39) + ' " and vice versa
If you need specific column names then on line 27 you have to add the column names (example ...+ ') AND Name=' + Char(39) + 'MyColumnName' + Char(39)

I don't know what else to say.
I think that I have explained all.

You can run it in order to see the output .
The script if you haven't change anything only prints and doesn't execute anything....

aspdevguy

ASKER

This is exactly what I was looking for! Thanks a lot!