Solved

changing data types across the database

Posted on 2013-01-23
2
193 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:
ScottPletcher 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 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

21 Experts available now in Live!

Get 1:1 Help Now