Solved

Change column data type from ntext to nvarchar

Posted on 2009-03-30
9
1,313 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:aspdevguy
  • 5
  • 4
9 Comments
 
LVL 12

Expert Comment

by:Dimitris
ID: 24026229
'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'
0
 

Author Comment

by:aspdevguy
ID: 24026268
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!
0
 

Author Comment

by:aspdevguy
ID: 24026284
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?

0
 
LVL 12

Expert Comment

by:Dimitris
ID: 24026330
So you need to update all the DBs
and all the tables that have an ntext column to nvarchar?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Accepted Solution

by:
Dimitris earned 500 total points
ID: 24026469
this script will print in the Query Analyzer
the SQL Scripts that you have to run
It will update all the columns of ntext and text to nvarchar(4000)





SET NOCOUNT ON

USE MASTER

DECLARE @tmpDB_NAME nvarchar(4000)

DECLARE @tmpTBL  nvarchar(4000),

		@tmpCOL  nvarchar(4000),

		@tmpTBL_SCHEMA  nvarchar(4000),

		@tmpSQL nvarchar(4000)

DECLARE @tmpDEF_END_LINE nvarchar(100)

SET @tmpDEF_END_LINE= CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
 

CREATE TABLE #tmp (TABLE_NAME nvarchar(1000),

				   TABLE_SCHEMA nvarchar(100),

				   COLUMN_NAME nvarchar(1000))
 

DECLARE rst CURSOR LOCAL FAST_FORWARD FOR

	select NAME from master..sysdatabases where sid<>1

OPEN rst

FETCH NEXT FROM rst INTO @tmpDB_NAME

WHILE @@FETCH_STATUS<>-1

BEGIN

	DELETE FROM #tmp

	SET @tmpSQL= '	INSERT INTO #tmp

					select 	TABLE_NAME,

							TABLE_SCHEMA,

							COLUMN_NAME 

					from 	[' + @tmpDB_NAME + '].INFORMATION_SCHEMA.COLUMNS 

					WHERE 	DATA_TYPE IN (' + char(39) + 'ntext' + char(39) + ' ,' + char(39) + 'TEXT' + char(39) + ')'

	EXECUTE (@tmpSQL)

	

	PRINT '--DATABASE : ' + @tmpDB_NAME +'********************************************************************' + CHAR(13) + CHAR(10)
 

	DECLARE rst2 CURSOR LOCAL FAST_FORWARD FOR

		select 	TABLE_NAME,

				TABLE_SCHEMA,

				COLUMN_NAME 

		from 	#tmp

	OPEN rst2

	FETCH NEXT FROM rst2 INTO

		@tmpTBL, @tmpTBL_SCHEMA, @tmpCOL

	WHILE @@FETCH_STATUS<>-1

	BEGIN

		SET @tmpSQL = 'USE [' + @tmpDB_NAME +'] ' + @tmpDEF_END_LINE +

					 'ALTER TABLE ['+ @tmpTBL +'] ADD [' + @tmpCOL +'_NvarChar] nvarchar(4000)' + @tmpDEF_END_LINE +

					 'UPDATE ['+ @tmpTBL +'] SET [' + @tmpCOL +'_NvarChar] = Convert(nvarchar(4000),[' + @tmpCOL +'])' + @tmpDEF_END_LINE +

					 'ALTER TABLE ['+ @tmpTBL +'] DROP COLUMN [' + @tmpCOL +']'+ @tmpDEF_END_LINE +

					 'EXEC sp_rename ' + char(39) + '[' + @tmpTBL +'].['+ @tmpCOL + ']'  + char(39) +',' + CHAR(39) + '[' + @tmpCOL +']' + char(39) +',' + CHAR(39) + 'COLUMN' + CHAR(39) + @tmpDEF_END_LINE 

					

					 

		PRINT @tmpSQL

		FETCH NEXT FROM rst2 INTO

			@tmpTBL, @tmpTBL_SCHEMA, @tmpCOL

	END

	CLOSE rst2

	DEALLOCATE rst2

	FETCH NEXT FROM rst INTO @tmpDB_NAME

END

CLOSE rst

DEALLOCATE rst

DROP TABLE #tmp

Open in new window

0
 
LVL 12

Expert Comment

by:Dimitris
ID: 24026472
P.S. IT WILL NOT EXECUTE THE SCRIPTS!
You can check them and run them when u want
0
 

Author Comment

by:aspdevguy
ID: 24029956
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!
0
 
LVL 12

Expert Comment

by:Dimitris
ID: 24033635
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....

0
 

Author Comment

by:aspdevguy
ID: 24035747
This is exactly what I was looking for! Thanks a lot!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now