Solved

convert datatypes in a table to nvarchar.

Posted on 2014-04-01
3
421 Views
Last Modified: 2014-04-01
I have a table with 184 fields that are all defines as varchar. Is there a way to convert all the fields to nvarchar?
Thanks in Advance.
0
Comment
Question by:tesla764
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39969283
you can run this query, and use the outcome of the column "sql" to run it:
select table_schema, table_name,column_name,  character_octet_length
, 'ALTER TABLE [' + table_schema + '].[' + table_name + '] alter column [' + column_name + '] nvarchar(' + cast(character_octet_length as varchar(10)) + ')' sql
from INFORMATION_SCHEMA.columns
where data_type = 'varchar'
and table_name = 'your_table_name'

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39969331
I'd do them all at once, or this could for an extremely long time, given that (almost) every update could potentially force another page split.

Since SQL has no native way to do this all at once, you'll need to create a separate conversion table.  We'll need to lock people out of the table until the conversion completes.

First, ccript out your existing table, then change the table name and all "varchar" to "nvarchar", then run the new CREATE TABLE:

-- original table name = 'table_name'
CREATE TABLE dbo.table_name_NVARCHAR (
    col1 nvarchar(...) ...,
    col2 nvarchar(...) ...,
    )

Then, something like this:

BEGIN TRY
BEGIN TRANSACTION
--SET IDENTITY_INSERT table_name ON
INSERT INTO dbo.table_name_NVARCHAR --( ...column_list... )
SELECT * --...column_list...
FROM dbo.table_name WITH (TABLOCKX)
--SET IDENTITY_INSERT table_name OFF
DROP TABLE dbo.table_name
EXEC sp_rename 'table_name_NVARCHAR', 'table_name'
COMMIT
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION
END CATCH
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39969332
Sorry, if I'd known you were in that big a hurry, I wouldn't have wasted my time trying to offer an alternative.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

895 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

19 Experts available now in Live!

Get 1:1 Help Now