Solved

changing data types across the database

Posted on 2013-01-23
2
201 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

695 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