Sql-server – Tool for converting all VARCHAR to NVARCHAR in one or few steps

sql servert-sqlvarchar

I am using SQL Server Management Studio and need to convert all VARCHAR columns to NVARCHAR. Is there a tool or something similar to do the conversion in one or few steps? It should be applied to different databases.

Addition:

Excuse me, but is it possible that you gave me a script to convert NVARCHAR to VARCHAR? I need it the other way round. Also when I run the script which was produced by your script it gave me the error

Meldung 102, Ebene 15, Status 1, Zeile 1
Falsche Syntax in der Nähe von 'GO'.

multiple times. Actually I thought that the solution your script produce, to simply alter the tables doesnt work when there are constraints related to tables. Thats why I asked whether there is a tool that can do this, because I thought a simple script has restrictions.

Best Answer

no tool needed just some good old TSQL code.

This will generate all the statements.

DECLARE @Statements table (Statement varchar(max))
DECLARE @CMD varchar(max)
DECLARE @DB varchar(500)
DECLARE c CURSOR for select distinct name from sys.databases where database_id > 4
OPEN C
FETCH NEXT FROM C into @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD = ' USE '+quotename(@DB)+';

select ''USE '+quotename(@DB)+' GO
ALTER TABLE ''+QUOTENAME(s.name)+''.''+QUOTENAME(t.name)+''
ALTER COLUMN ''+quotename(c.name)+'' varchar(''+cast(c.max_length as varchar)+'')
GO'' from '+QUOTENAME(@DB)+'.sys.tables t
inner join '+QUOTENAME(@DB)+'.sys.schemas s on s.schema_id = t.schema_id
inner join '+QUOTENAME(@DB)+'.sys.columns c on c.object_id = t.object_id
where is_ms_shipped = 0
and system_type_id = 231
'

INSERT INTO @Statements
EXEC (@CMD)

FETCH NEXT FROM C into @DB

END
CLOSE C
DEALLOCaTE C

SELECT * from @Statements
Related Question