converting nvarchar columns datatype to varchar datatype

If your database fields does not need to support unicode (internatioal characters ex:Japanese) then you can change nvarchar to varchar. But I would not modify any system tables to accomplish this as modifying system tables may be risky. I would generate sql script for all tables and then do "Find/Replace" to replace all nvarchar to varchar and recreate the tables. Then I would insert the data from oracle to sql server.

You could do this through the system tables, make a backup first. It is slightly risky to modify system tables so you need to thoroughly test this. Look up the syscolumns table to create your update.

Alternatively you could actually do a modification of namasi's solution:

make a cursor to loop through all your tables
within that cursor loop through the tables columns
 (look at the syscolumns and sysobjects table)
and use dynamic SQL to issue an alter table alter column statement to change the data type.

This method will change ALL columns in your database to varchar where they are nvarchar.

run that in the database you are working with.

also change the datatypes. I was testing on a test database that already had varchar, so i looked for varchar columns and changed them to nvarchar. you'll want to do the reverse.

For example, If I use the query like below to generate the command that you need it is including a schema table too... There may be a flag to identify user created tables only... I will look into..


select 'Alter Table ' + t.table_name + ' Alter Column ' + c.column_name + ' varchar(' + cast( (character_maximum_length /2) + 1 as varchar)+ ')'
from  INFORMATION_SCHEMA.Columns c, pubs.INFORMATION_SCHEMA.Tables t
where c.Table_Name = t.Table_Name and
       c.Data_Type = 'nvarchar'

missed a variable declaration in cut and paste somehow. use this one

set nocount on
declare @ColumnName varchar(255)
declare @TableName  varchar(255)
declare @strSQL nvarchar(255)

DECLARE curFixColsGetTables CURSOR
FOR SELECT TABLE_NAME
FROM information_schema.tables
WHERE table_Type = 'Base Table'

OPEN curFixColsGetTables
FETCH NEXT FROM curFixColsGetTables into @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
 
  DECLARE curFixColsGetCols CURSOR
  FOR SELECT COLUMN_NAME
  FROM information_schema.columns
  WHERE table_name = @TableName AND DATA_TYPE = 'varchar'

  OPEN curFixColsGetCols
  FETCH NEXT FROM curFixColsGetCols INTO @ColumnName
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @strSQL = 'ALTER TABLE ' + @TableName
           + ' ALTER COLUMN ' + @ColumnName
           + ' nvarchar(500)'
    PRINT @strSQL
    EXEC  SP_EXECUTESQL @strSQL

    FETCH NEXT FROM curFixColsGetCols
    INTO @ColumnName
  END
  CLOSE curFixColsGetCols
  DEALLOCATE curFixColsGetCols
 
  FETCH NEXT FROM curFixColsGetTables
  INTO @TableName
  END
CLOSE curFixColsGetTables
DEALLOCATE curFixColsGetTables

actually if you run a select from information_schema.tables, it will only show you non system tables, with the exception of the DTproperties table, so you need to exclude that table in the where clause in my above script (sorry, i missed it.. should be sleeping!!) So modify the cursor declaration for the top cursor:

DECLARE curFixColsGetTables CURSOR
FOR SELECT TABLE_NAME
FROM information_schema.tables
WHERE table_Type = 'Base Table'
AND table_name <> 'dtproperties'

now you will not get any system views (or any views) because you have a table_type of
base table, and you will not get the one system table that doesn't begin with sys (dtproperties)

make the change to the above and test my script, it should do exactly what you need it to once you make the other change of reversing your varchar with nvarchar.

Also, <> myschematable and <> dtproperties.

This seems to include user created table only. But doble make sure after generating the commands..
select 'Alter Table ' + t.table_name + ' Alter Column ' + c.column_name + ' varchar(' + cast( (character_maximum_length /2) + 1 as varchar)+ ')'
from  INFORMATION_SCHEMA.Columns c, pubs.INFORMATION_SCHEMA.Tables t
where c.Table_Name = t.Table_Name and
       c.Data_Type = 'nvarchar' and t.table_name <> 'myschematable' and
      t.table_name <> 'dtproperties'

myschematable never showed up as one of mine, but yes always look at your statements and good idea to just run through it once as a select.

Well, you can also just copy the result of the select posted above and just execute it than creating a stored proc to run the alter table commands.

HTH

yeah both will work.

Yes. If you need it for future you might as well create a procedure to do the job!!!

don't forget to update your stats afterwards...

you may also want to just generate t scripts from the above examples and then

run them you self ...

you may want to aggregate the changes to a particular table

you may also want to consider how to make any modifications if they affect key fields....

would it be better to make some of the colums char rather than varchar (less overhead)

do you have any stored procedures / views that will also need modification...


hth good luck....

thanks guys