Solved

Change column data type from ntext to nvarchar

Posted on 2009-03-30
9
1,326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
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
 
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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about achieving the basic levels of HRIS security in the workplace.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…

710 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