Solved

changing data types across the database

Posted on 2013-01-23
2
196 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
2 Comments
 
LVL 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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