Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert all datatype nchar to nvarch on database

Posted on 2011-04-20
1
Medium Priority
?
282 Views
Last Modified: 2012-05-11
I want to create a SQL statement that does the following

a) converts all nchar to nvarchar
b) trims off extra spaces from the fixed width:  RTRIM(LTRIM(xxxxx_fieldname))
c) performs this action only on database tables, not views

I know the following select statement gets the schema, but I have no idea how to perform the actions above on each field--can someone help?

select TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, ORDINAL_POSITION
from information_schema.columns
where data_type = 'nchar'
order by 1,3
0
Comment
Question by:saturation
1 Comment
 
LVL 9

Accepted Solution

by:
radcaesar earned 2000 total points
ID: 35436097
Generate alter script using a tool / using T-SQL

http://www.sqlservercentral.com/Forums/Topic645738-8-1.aspx

Find and replace all nchar to nvarchar

Add the LTRIM/RTRIM where required

Execute



0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
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…

810 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