Script to change a nvarchar columns to varchar col

The output of the below script will helps u to change nvarchar column to varchar
column.

select "alter table " + isc.table_name + " alter column " +isc.column_name+" varchar(" +convert(varchar,sc.length)+')' +char(13)+'go'
FROM sysobjects so,
syscolumns sc,
information_schema.columns isc
WHERE
isc.table_name = 'employees'
and
isc.table_name = so.name
AND so.id = sc.id
and
isc.COLUMN_NAME=sc.name
and
data_type='nvarchar'

Note: Here employees table in nothwind has taken as example to alter all nvarchar columns to varchar and u cant alter a column which was already indexed.

Thanks.


Rushendra

you can remove the line "isc.table_name = so.name" to get the queries for all the tables which have nvarchar

like

select 'alter table ' + isc.table_name + ' alter column ' +isc.column_name+' varchar(" +convert(varchar,sc.length/2)+')' +char(13)+'go'
FROM sysobjects so,
syscolumns sc,
information_schema.columns isc
WHERE
isc.table_name = so.name
AND so.id = sc.id
and
isc.COLUMN_NAME=sc.name
and
data_type='nvarchar'

Kumaran
BI