SQL 数据库排序规则修改和 数据库表列排序规则修改

--获取服务器排序规则名称
SELECT SERVERPROPERTY ('Collation') 

---数据库排序规则修改

ALTER DATABASE SRMain  SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Go
ALTER DATABASE SRMain COLLATE Chinese_PRC_CI_AS
Go
ALTER DATABASE SRMain SET MULTI_USER
Go

----数据库表列排序规则修改
select ta.name,c.name,c.is_nullable,c.max_length,c.collation_name,t.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_CI_AS ' 
+ cast( case c.is_nullable when 0 then ' Not NULL ' else ' NULL ' END as nvarchar)
+ ' ;'
as ds
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 = N'SQL_Latin1_General_CP1_CI_AS'
      --and t.name like N'v%'
--and c.name = 'ntext'
order by ta.name asc