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

王朝other·作者佚名  2006-01-08
窄屏简体版  字體: |||超大  

/*--将所有的表中,数值类型由char,varchar改为nchar,nvarchar 的存储过程--*/

/*--调用示例:

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航