红联Linux门户
Linux帮助

批量将一个库里的所有表里的char改成nchar类型

发布时间:2007-03-26 09:36:23来源:红联作者:earwig
/*--将所有的表中,数值类型由char,varchar改为nchar,nvarchar 的存储过程

--邹建 2004.02(引用请保留此信息)--*/

/*--调用示例:
exec p_set
--*/
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

create procedure p_set
as
declare tb cursor for
SELECT sql='alter table ['+d.name
+'] alter column ['+a.name+'] n'
+b.name+'('+cast(a.length*2 as varchar)+')'
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where
b.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 = a.id AND colid=a.colid
))) --主键不能修改
order by d.name,a.name

declare @sql varchar(1000)
open tb
fetch next from tb into @sql
while @@fetch_status = 0
begin
exec(@sql)
fetch next from tb into @sql
end
close tb
deallocate tb
go
文章评论

共有 78 条评论

  1. 190.74.107.* 于 2007-07-17 11:02:45发表:

    http://e3c34c32296369aa5f3170b1ec6fa54c-t.xkktxb.org e3c34c32296369aa5f3170b1ec6fa54c http://e3c34c32296369aa5f3170b1ec6fa54c-b1.xkktxb.org e3c34c32296369aa5f3170b1ec6fa54c http://e3c34c32296369aa5f3170b1ec6fa54c-b3.xkktxb.org 8d1f2bfe3cbc5359328d95464cab8b7c