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;
END

GO

SELECT *     FROM  INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME='Chinese_PRC_CI_AS'
GO    

DROP TABLE TEST

------解决方案--------------------
SQL code
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

------解决方案--------------------

SQL code
 
---以下为刚编写的，未进行大规模测试，操作前请先备份数据库，以便出问题时还原。有问题回贴。
--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 fo