SQL批量更改用户表内字符型字段的排序规则

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-23 20:33:15
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--          Jul  9 2008 14:43:34
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--  Blog   : http://blog.csdn.net/htl258
--  Subject: 批量更改用户表内字符型字段的排序规则
--------------------------------------------------------------------------

 

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' 
    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

--注：主键约束与外键约束的字段在此代码中没考虑。

 

通过INFORMATION_SCHEMA.COLUMNS的查询方式

----------------------------------------------------------------------------------

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' 
    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