修改SQL数据库排序规则修改表栏位排序规则

-- ============================================= -- create procduce//p_coloumn_collate(调整表栏位排序方式) -- ============================================= if exists (select * from sysobjects where id =object_id(N'p_coloumn_collate') and type='P') drop procedure dbo.p_coloumn_collate go --exec p_coloumn_collate '%','%','Chinese_Taiwan_Stroke_CI_AS','Chinese_PRC_CS_AI' create procedure dbo.p_coloumn_collate @as_tablename varchar(50)='%', @as_notliketablename varchar(50)='', @as_ori_collation_name varchar(50)='Chinese_Taiwan_Stroke_CI_AS', @as_new_collation_name varchar(50)='Chinese_PRC_CS_AI', @as_exe_flag varchar(50)='0' -- 1 直接執行sql,0 不執行sql --Chinese_Taiwan_Stroke_CI_AS --Chinese_PRC_CS_AI --with encryption --加密 as begin --declare @lt_table table (ftablename varchar(150) null,ftablenote varchar(5000) null,ftype varchar(5)) declare @lt_column table (fid int identity(1,1),fcolumn varchar(8000) null, fsort varchar(5) ) declare @lt_exesql table (fid int ,fexeremark varchar(20) ) declare @ls_developtool varchar(15),--连接开发工具 @ls_table varchar(150), @ls_tablenote varchar(250), @ls_type varchar(20), @ls_columnname varchar(50), @ls_d_fk varchar(8000), @ls_d_pk varchar(8000), @ls_sql varchar(8000), @ls_g_fk varchar(8000), @ls_g_pk varchar(8000), @ls_pkname varchar(100), @ls_fkname varchar(100), @ls_indexname varchar(150), @ls_indexcol varchar(150), @ls_note varchar(150), @ls_sort varchar(5), @ls_fktype varchar(5), --外键被引用 @ls_sql_id int --把数据全部转入当前定义的表来处理 --若使用表之间关联可能会报错 因为db排序与表栏位排序可能不一样的情况 --表 create table #lt_table( ftablename varchar(150) null,ftablenote varchar(5000) null,ftype varchar(5) ) insert into #lt_table select a.name,a.name,a.type from sysobjects a where (a.type='U' ) and (a.name like @as_tablename or @as_tablename='%') and a.name not like @as_notliketablename order by a.name asc create table #tablecolumn( ftable varchar(250) null,fcolumn varchar(250) null, fcollation_name varchar(50) null,fg_sql varchar(2000) null, fcolid int null) create table #tablekey( ftable varchar(250) null,fkeytype varchar(250) null, fkeyname varchar(50) null, fkeycol varchar(300) null, fd_sql varchar(8000) null, fg_sql varchar(8000) null ) --索引 create table #lt_keyindex (findex_name varchar(1000) null , findex_note varchar(1000) null , findex_keys varchar(6000) null) --索引 create table #tableindex( ftable varchar(250) null, fkeyname varchar(50) null, fkeycol varchar(300) null, fd_sql varchar(8000) null, fg_sql varchar(8000) null ) insert into #tablecolumn select ta.name,c.name,c.collation_name, 'Alter Table dbo.[' + CAST(ta.name as nvarchar) +'] Alter Column [' + CAST(c.name as nvarchar) + '] ' + CAST(t.name as nvarchar) + case when t.name = N'ntext' or t.name = 'text' then '' when c.max_length = -1 then '(max)' when t.name like N'n%' then '('+CAST(c.max_length/2 as nvarchar)+')' else '('+CAST(c.max_length as nvarchar)+')' end --+ ' COLLATE Chinese_PRC_CS_AI ' + ' Collate '+ @as_new_collation_name + cast( case c.is_nullable when 0 then ' Not Null ' else ' Null ' END as nvarchar) --+ char(10)+'go' +' --sql' as g_sql, c.column_id from sys.tables ta inner join sys.columns c on ta.object_id = c.object_id inner join sys.types t on c.system_type_id = t.system_type_id where t.name <> N'sysname' and c.collation_name =@as_ori_collation_name --N'Chinese_Taiwan_Stroke_CI_AS' and exists(select 1 from sysobjects a where a.name=ta.name and (a.type='U' ) and (a.name like @as_tablename or @as_tablename='%') and a.name not like @as_notliketablename ) --and exists(select 1 from #lt_table e_tb where e_tb.ftablename=ta.name) --and CAST(ta.name as nvarchar) like 'sys_prgtable_list%' --and CAS�