if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_set]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_set]
GO
exec p_set
--*/
create procedure p_set
@flag bit=0 --转换标志:
--0 转换时使用原来的定义长度,如果原来定义长度超过4000,则定义为4000
--1 转换时使用原来的定义长度/2
as
declare tb cursor local for
SELECT N'alter table ['
+replace(o.name,N']',N']]')
+N'] alter column ['
+replace(c.name,N']',N']]')
+N'] N'
+replace(t.name,N']',N']]')
+N'('
+case
when @flag=0
then case when c.prec>4000 then '4000' else rtrim(c.prec) end
else rtrim(c.prec/2)
end+N')'
FROM syscolumns c,systypes t,sysobjects o
where o.id=c.id
and c.xusertype=t.xusertype
and objectproperty(o.id,'IsUserTable')=1
and o.status>=0
and t.name in('char','varchar') --要处理的数据类型
and not exists(
SELECT 1 FROM sysobjects
where xtype='PK'
and name in (
SELECT name FROM sysindexes
WHERE indid in(
SELECT indid FROM sysindexkeys
WHERE id = c.id AND colid=c.colid))) --主键不能修改
order by o.id,c.colid
declare @s nvarchar(4000)
open tb
fetch tb into @s
while @@fetch_status = 0
begin
exec(@s)
fetch tb into @s
end
close tb
deallocate tb
go