declare @schema table (tblName varchar(128), colName varchar(64), colLen int)
insert into @schema
select SO.Name, SC.name, convert(int, sc.length) as Length from sysobjects SO
join syscolumns SC on SC.id = so.id
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 SO.name like 'tbl%'
order by SO.name, SC.name
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
begin
--
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
END
close change
deallocate change
Wednesday, December 31, 2008
Convert change alter NVarchar and Text to Varchar
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment