1.char,varchar转到nchar,nvarchar
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
/**/ /*--将所有的表中,数值类型由char,varchar改为nchar,nvarchar
--*/
/**/ /*--调用示例:
exec p_set
--*/
-- 修改的存储过程
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
drop procedure [ dbo ] . [ p_set ]
GO
/**/ /*--将所有的表中,数值类型由char,varchar改为nchar,nvarchar
--*/
/**/ /*--调用示例:
exec p_set
--*/
-- 修改的存储过程
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
2.varchar转到nvarchar
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
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
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 = ' 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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
drop procedure [ dbo ] . [ p_set ]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
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 = ' 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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO