Solved

changing data types across the database

Posted on 2013-01-23
2
199 Views
Last Modified: 2013-02-12
when i used SELECT * INTO NEWTABLE FROM OLDTABLE

it recreates the VARCHAR fields into NVARCHAR..

there are many such tables now in the database. Is it possible to correct them all through a UPDATE or ALTER Query?
0
Comment
Question by:25112
[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
2 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 38810808
Modifying the data type of a column is done via ALTER TABLE statment. Question is why you wAnt to change?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 450 total points
ID: 38810897
>> it recreates the VARCHAR fields into NVARCHAR <<

Really?  I haven't seen that happen.

You could easily generate the needed ALTER TABLE commands.  For example, as below:


SELECT 'ALTER TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] ' +
    'ALTER COLUMN [' + c.name + '] ' + CAST(SUBSTRING(ty.name COLLATE SQL_Latin1_General_Cp1_CI_AS, 2, 100) AS varchar(30)) +
    '(' + CASE WHEN c.max_length = '-1' THEN 'MAX' ELSE CAST(c.max_length AS varchar(4)) END + ')'
FROM sys.columns c
INNER JOIN sys.tables t ON
    t.object_id = c.object_id
INNER JOIN sys.types ty ON
    ty.user_type_id = c.user_type_id
WHERE
    t.name LIKE 'sys%' AND
    ty.name LIKE 'n%char%' AND
    c.is_computed = 0


Given that the table could shrink considerably, you probably want to rebuild the tables after the ALTERs.
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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