1

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.

1 Answer 1

1

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
1
  • what about varchar(max)? that will give an error using cast(c.max_length as varchar).... Apr 9, 2019 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.