Wednesday, December 31, 2008

Convert change alter NVarchar and Text to Varchar

declare @schema table (tblName varchar(128), colName varchar(64), colLen int)
insert into @schema
select SO.Name,, convert(int, sc.length) as Length from sysobjects SO
join syscolumns SC on =
where type_name(SC.xusertype) in ('text', 'nvarchar')
and SC.Name NOT in
(select name from syscolumns where name like 'DF_%')
and SO.xtype <> 'P' and like 'tbl%'
order by,
select * from @schema

declare @tName varchar(128), @cName varchar(128), @len int

declare @altString nvarchar(512)
declare Change Cursor for select * from @schema
open Change
fetch next from change into @tName, @cName, @len
while @@FETCH_STATUS = 0
set @altString = 'alter table ' + @tName + ' Alter Column ' + @cName + ' varchar(' + cast(@len / 2 as Varchar(4)) + ')'
EXECUTE sp_executeSQL @altString
fetch next from change into @tName, @cName, @len
close change
deallocate change

No comments: