关于调整数据库排序规则问题

Question

数据库原来的排序规则为:Chinese_PRC_CI_AS,不区分全角和半角现在通过更改数据库排序规格更改为:Chinese_PRC_CI_AS_WS,区分全角半角排序规则但是现在进入各个表中的列看,有很多排序规则依然为Chinese_PRC_CI_AS,这有可能是原来建立表的时候指定了排序规则现在我要怎样把所有表的列全部更改为同一种排序规则?感谢!

Answer 1

select* from INFORMATION_SCHEMA.COLUMNS
where COLLATION_NAME='Chinese_PRC_CI_AS_WS'
先查出来。。写个循环

Answer 2

alter database dbname collate Chinese_PRC_CI_AS_WS

Answer 3

不知道我直接更改 COLLATION_NAME 列的值为 'Chinese_PRC_CI_AS_WS'
会是什么结果

Answer 4

CREATE TABLE TEST
(ID VARCHAR(10)   COLLATE Chinese_PRC_CI_AS,
 ID1 VARCHAR   COLLATE Chinese_PRC_CI_AS,
 ID2 VARCHAR(MAX)   COLLATE Chinese_PRC_CI_AS,
 ID3 NVARCHAR(10)   COLLATE Chinese_PRC_CI_AS) GO 
  
SELECT *  FROM  INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME='Chinese_PRC_CI_AS'
GO
  
 WHILE 1=1
 BEGIN
DECLARE  @TABLE_NAME   NVARCHAR(50) ,@COLUMN_NAME  NVARCHAR(50),@TYPE  NVARCHAR(50)

SELECT  TOP 1 @TABLE_NAME=TABLE_NAME,@COLUMN_NAME=COLUMN_NAME
,@TYPE=DATA_TYPE+CASE CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN '(max)' 
WHEN 1 THEN ''
ELSE '('+LTRIM(CHARACTER_MAXIMUM_LENGTH)+')'END 
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME='Chinese_PRC_CI_AS' IF @@ROWCOUNT>0
BEGIN 
EXEC ('ALTER TABLE '+@TABLE_NAME+' ALTER COLUMN '+@COLUMN_NAME+' '+ @TYPE+' COLLATE Chinese_PRC_CI_AS_WS');
END

ELSE BREAK;
ENDGOSELECT *  FROM  INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME='Chinese_PRC_CI_AS'
GO DROP TABLE TEST

Answer 5

ALTER DATABASE dbname COLLATE Chinese_PRC_CI_AS_WS
GO
SET NOCOUNT ON
DECLARE @S NVARCHAR(1000)
DECLARE C CURSOR FOR --不区分大小写
    SELECT 'ALTER TABLE ['+B.NAME+'] ALTER COLUMN ['+A.NAME+'] '+ TYPE_NAME(A.XTYPE)+
        CASE WHEN TYPE_NAME(A.XTYPE) IN('TEXT','NTEXT') THEN '' ELSE 
            QUOTENAME(A.LENGTH,'(')
        END +' COLLATE CHINESE_PRC_CI_AS_WS'  
    FROM SYSCOLUMNS A 
        JOIN SYSOBJECTS B ON A.ID=B.ID AND B.TYPE='U'
    WHERE TYPE_NAME(A.XTYPE) IN('VARCHAR','CHAR','NVARCHAR','NCHAR','TEXT','NTEXT')
OPEN C
FETCH C INTO @S
WHILE @@FETCH_STATUS=0
BEGIN
    EXEC(@S)
    FETCH C INTO @S
END
CLOSE C
DEALLOCATE C
GO

Answer 6

消息 5074，级别 16，状态 1，第 2 行
对象'PK_Jd_cenpin_dtl' 依赖于 列'bill_type'。
消息 4922，级别 16，状态 9，第 2 行
由于一个或多个对象访问此列，ALTER TABLE ALTER COLUMN bill_type 失败。更改时出现这些东西 东西 ,不明白

Answer 7

 
---以下为刚编写的，未进行大规模测试，操作前请先备份数据库，以便出问题时还原。有问题回贴。
--1.生成主键约束脚本并引出
 SELECT 'ALTER TABLE '
 + QUOTENAME(a.TABLE_NAME)
 + ' ADD CONSTRAINT '
 + a.CONSTRAINT_NAME
 + ' PRIMARY KEY ('
 + QUOTENAME(COLUMN_NAME)
 +');'
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
ON a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
 WHERE CONSTRAINT_TYPE='PRIMARY KEY'
 
--2.生成外键约束脚本并引出 
SELECT 'ALTER TABLE '
    + QUOTENAME(OBJECT_NAME(a.PARENT_OBJECT_ID)) --表名
    + ' ADD CONSTRAINT '
    + OBJECT_NAME(a.OBJECT_ID) --约束名
    + ' FOREIGN KEY ('
    + QUOTENAME(c.name) --字段名
    + ') REFERENCES '
    + QUOTENAME(OBJECT_NAME(a.REFERENCED_OBJECT_ID))--被引用表名
    + ' ('
    + QUOTENAME(d.name)--被引用字段名
    + ')'
    + CASE WHEN a.delete_referential_action=1
   THEN ' ON DELETE CASCADE ' 
   ELSE ''
      END
    + CASE WHEN update_referential_action=1
   THEN ' ON UPDATE CASCADE ' 
   ELSE ''
      END
    +';' AS [Foreing Key SQL]
FROM sys.foreign_keys a
JOIN  sys.foreign_key_columns b
ON a.[object_id]=b.constraint_object_id
JOIN sys.[columns] c
ON b.parent_object_id=c.[object_id]
AND b.parent_column_id=c.column_id
join sys.[columns] d
ON b.referenced_object_id=d.[object_id]
AND b.referenced_column_id=d.column_id
 
 --3.删除外键约束
 SET NOCOUNT ON 
 DECLARE c1 cursor for 
     select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
     from sysobjects 
     where xtype = 'F'
 open c1
 declare @c1 varchar(8000)
 fetch next from c1 into @c1
 while(@@fetch_status=0)
     begin 
         exec(@c1)
         fetch next from c1 into @c1
     end
 close c1
 deallocate c1 
 GO
 --4.删除主键约束
  SET NOCOUNT ON 
 DECLARE c1 cursor for 
     select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
     from sysobjects 
     where xtype = 'PK'
 open c1
 declare @c1 varchar(8000)
 fetch next from c1 into @c1
 while(@@fetch_status=0)
     begin 
         exec(@c1)
         fetch next from c1 into @c1
     end
 close c1
 deallocate c1 
 GO
 
 --5.修改排序规则
 SET NOCOUNT ON 
 DECLARE @S NVARCHAR(1000)
 DECLARE C CURSOR FOR --不区分大小写
     SELECT 'ALTER TABLE ['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] '
         + DATA_TYPE
         + CASE WHEN DATA_TYPE IN('TEXT','NTEXT') THEN '' ELSE 
               QUOTENAME(CHARACTER_MAXIMUM_LENGTH,'(')
            END 
         + ' COLLATE CHINESE_PRC_CI_AS_WS '  
         + CASE IS_NULLABLE WHEN 'NO' THEN ' NOT NULL ' ELSE '' END
     FROM INFORMATION_SCHEMA.COLUMNS
  WHERE DATA_TYPE IN('varchar','nvarchar','char','nchar','text','ntext')
 OPEN C
 FETCH C INTO @S
 WHILE @@FETCH_STATUS=0
 BEGIN
     EXEC(@S)
     FETCH C INTO @S
 END
 CLOSE C
 DEALLOCATE C
 GO
--6.重新创建主键约束(执行导出的脚本) --7.重新创建外键约束脚本(执行导出的脚本) 

Answer 8

索引脚本生成代码参考：http://blog.csdn.net/htl258/archive/2009/03/12/3984606.aspx