Converting Unicode column types to non-unicade

[sparkbyte]

Has anyone seen or written a script to change a unicode column to its non-unicode equiv??

I have inherited a somewhat large database that is really just a collection of tables from various Access Applications that got overgrown and had to be moved to SQL Server to overcome space limitations. Problem is that the person that moved the tables to the SQL server did not check the column type mappings before running the wizard and now I have a database with 100's of unicoce varchar fields (nVarChar()) that should not have been unicode. And to make matters a tad more difficult Access maps its Memo column type to nText on the SQL side. So these now need to be changed also to VarChar(Max).

Any way to automate this would be great!!

Thanks

John Fuhrman

 

[bborissov]

DECLARE @Test TABLE(Id int IDENTITY(1,1), TableName NVarchar(200), ColumnName NVarchar(200), ColumnType NVarchar(200), ColumnLenght int)
INSERT INTO @Test (TableName, ColumnName, ColumnType, ColumnLenght)
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('varchar', 'text')
SELECT * FROM @Test

DECLARE @i_min int
SET @i_min = 1
DECLARE @i_max int
DECLARE @SQL         nvarchar(4000)
DECLARE @TableName    nvarchar(4000)
DECLARE @ColumnName   nvarchar(4000)
DECLARE @ColumnType   nvarchar(4000)
DECLARE @ColumnLenght int

SELECT @i_max = MAX(Id) FROM @Test

WHILE @i_min <= @i_max
      BEGIN  
          SELECT @TableName = TableName,
                 @ColumnName = ColumnName,
                 @ColumnType = ColumnName,
                 @ColumnLenght = ColumnLenght
          FROM @Test 
          WHERE Id = @i_min
          SET @SQL = 'ALTER TABLE ['+ @TableName + '] ALTER COLUMN ['+@ColumnName 
          IF CHARINDEX('text', @ColumnType) > 0 OR
             @ColumnLenght < 0
             SET @SQL = @SQL + '] varchar(max)'
          ELSE
             SET @SQL = @SQL + '] varchar('+CAST(@ColumnLenght as varchar(200))+')'
             
         EXEC (@sql)
      
        SET @i_min = @i_min + 1 
      END

NOT TESTED!!!!!!!!!!!!
Also didn't check for constraints etc.
This script is only to see how you could do it



Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.