Would anyone know how I could update a data type from nvarchar to varchar for
an entire database of tables? I don't want to have to manually open each
table and replace the field data types. Please advise.

Amytdev

I would add a new varchar column, copy the data from the nvarchar colum into
the new column, drop old column, and rename it.

Something like this:
UPDATE myTable SET NewColumn = oldcolumn
Drop your column, and rename the new one


Show quoteHide quote

AmytDev (Amyt***@discussions.microsoft.com) writes:
   SELECT 'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name +
           ' nvarchar(' + ltrim(str(c.length)) + ') ' +
           CASE columnproperty(c.id, c.name, 'AllowsNull')
                WHEN 1 THEN ''
                WHEN 0 THEN 'NOT '
           END + 'NULL'
   FROM   sysobjects o
   JOIN   syscolumns c ON o.id = c.id
   WHERE  type_name(c.xtype) = 'varchar'
     AND  o.type = 'U'

Copy and paste result into a query window and run. In practice it is
not going to be this easy, you will first need to drop all indexes,
primary and foreign key and probably also check constraints and defaults.

So the best option may be to script the database and then run a replace
tool on it to change all occurrences of varchar to nvarchar. This also
handles stored procedures which may have variables and temp tables with
varchar.

LEFT JOIN... WHERE vs AND ?
Optimizing this crazy setup...
how to pass in a long string to stored procedure
Need Help With a Simple Query
Error converting data type varchar to numeric