Convert all datatype nchar to nvarch on database

Posted on 2011-04-20
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?

from information_schema.columns
where data_type = 'nchar'
order by 1,3
Question by:saturation
    1 Comment
    LVL 9

    Accepted Solution

    Generate alter script using a tool / using T-SQL

    Find and replace all nchar to nvarchar

    Add the LTRIM/RTRIM where required



    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    755 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

    17 Experts available now in Live!

    Get 1:1 Help Now