SQL Server Performance

Script to change a nvarchar columns to varchar col

Discussion in 'Contribute Your SQL Server Scripts' started by rushmada, Jul 8, 2003.

  1. rushmada New Member

    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
  2. kumaran New Member

    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

Share This Page