Improve company productivity with a Business Account.Sign Up

x
?
Solved

changing data types across the database

Posted on 2013-01-23
2
Medium Priority
?
219 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 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 70

Accepted Solution

by:
Scott Pletcher earned 1800 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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

587 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