declare @columnname varchar(100) --要修改的列的列名 declare @collationname varchar(100) --排序规则名select @columnname = 'AccountGroup' ,@collationname = 'Chinese_PRC_CI_AS'declare @tableName varchar(100) declare table_name cursor for select name from sys.objects where object_id in ( select object_id from sys.columns where name = @columnname ) and type = 'u'declare @Columntype varchar(120) --这个列的数据类型 open table_name fetch next from table_name into @tablename while @@fetch_status = 0 begin select @Columntype = c.name --数据类型 from sys.columns a inner join sys.objects b on a.object_id = b.object_id inner join sys.types c on a.system_type_id = c.system_type_id where b.name = @tablename and a.Name = @columnnameexec('alter table ' + @tablename + ' alter column '+ @columnname + @Columntype + ' COLLATE ' + @collationname) fetch next from table_name into @tablename end close table_name deallocate table_name
手动更新数据库系统表 Syscolumns
1楼的只是SQL2005里面做的 2000可以这样:alter table 表名 alter column 字段名 varchar(50) collate Chinese_PRC_CI_AS
用动态语句修改数据库同一字段名的排序规则 declare @SqlStr varchar(8000) select @SqlStr='' select @SqlStr=@SqlStr+' alter table ['+a.name+'] alter column ['+b.name+'] varchar(50) collate Chinese_PRC_CI_AS'+CHAR(10) from sysobjects a , syscolumns b where a.id=b.id and a.type='U' and b.name='字段名'print @SqlStr -- 用于查看执行过程 Exec(@SqlStr)
declare @collationname varchar(100) --排序规则名select @columnname = 'AccountGroup'
,@collationname = 'Chinese_PRC_CI_AS'declare @tableName varchar(100)
declare table_name cursor for
select name from sys.objects
where object_id
in
(
select object_id from sys.columns where name = @columnname
)
and type = 'u'declare @Columntype varchar(120) --这个列的数据类型
open table_name
fetch next from table_name into @tablename
while @@fetch_status = 0
begin
select @Columntype = c.name --数据类型
from sys.columns a inner join sys.objects b
on a.object_id = b.object_id
inner join sys.types c
on a.system_type_id = c.system_type_id
where b.name = @tablename
and a.Name = @columnnameexec('alter table ' + @tablename + ' alter column '+ @columnname + @Columntype + ' COLLATE ' + @collationname)
fetch next from table_name into @tablename
end
close table_name
deallocate table_name
Syscolumns
2000可以这样:alter table 表名 alter column 字段名 varchar(50) collate Chinese_PRC_CI_AS
declare @SqlStr varchar(8000)
select @SqlStr=''
select @SqlStr=@SqlStr+' alter table ['+a.name+'] alter column ['+b.name+'] varchar(50) collate Chinese_PRC_CI_AS'+CHAR(10)
from sysobjects a , syscolumns b
where a.id=b.id and a.type='U' and b.name='字段名'print @SqlStr -- 用于查看执行过程
Exec(@SqlStr)