--修改数据库的排序规则以及数据库中所有类型为 CHAR、VARCHAR、NCHAR 和 NVARCHAR 的列的排序规则

--USE OCULAR3
--GO

DECLARE @COLLNAME VARCHAR(255)
DECLARE @DBSNAME VARCHAR(255)
--DECLARE @COL VARCHAR(255)

SET @DBSNAME='OCULAR3'	
SET @COLLNAME=CONVERT(VARCHAR(255),DATABASEPROPERTYEX ( 'MASTER' , 'COLLATION' ))

IF (@COLLNAME NOT LIKE '%CI%')
	PRINT 'Master Collation don`t Like ''CI'''


--修改数据库的排序规则
EXEC('ALTER DATABASE '+@DBSNAME+' COLLATE  '+@COLLNAME)

--开始修改列的排序规则
IF OBJECT_ID('TEMPDB..#TABLE') IS NOT NULL
	DROP TABLE #TABLE
CREATE TABLE #TABLE
(
	TABLENAME SYSNAME,
	COLNAME SYSNAME,
	XTYPE TINYINT
)

DECLARE @TABLE VARCHAR(255)
DECLARE @COLNAME VARCHAR(255)
DECLARE @XTYPE INT
DECLARE @TYPE VARCHAR(255)

INSERT INTO #TABLE
	SELECT OBJECT_NAME(ID),NAME,XTYPE
	 FROM SYSCOLUMNS WHERE EXISTS
		(SELECT * FROM SYSOBJECTS WHERE ID= SYSCOLUMNS.ID AND XTYPE='U')
	AND COLLATION IS NOT NULL ORDER BY NAME

DECLARE CUR_TABLE CURSOR LOCAL FOR
	SELECT * FROM #TABLE
OPEN CUR_TABLE
FETCH NEXT FROM CUR_TABLE INTO @TABLE,@COLNAME,@XTYPE

WHILE @@FETCH_STATUS = 0
BEGIN
	IF (@XTYPE=231)
	BEGIN	
		SET @TYPE='NVARCHAR(255)'
		PRINT @TABLE+'\'+@COLNAME+'\'+@TYPE
		EXEC('ALTER TABLE [' + @TABLE + '] ALTER COLUMN ' + @COLNAME + ' ' + @TYPE + ' COLLATE '+@COLLNAME)
	END
	IF (@XTYPE=99)
	BEGIN
		BEGIN TRANSACTION
		SET @TYPE='NTEXT'
		PRINT @TABLE+'\'+@COLNAME+'\'+@TYPE
		PRINT 'ALTER TABLE [' + @TABLE + '] ADD [TEMP_' + @COLNAME + '] ' + @TYPE + ' COLLATE '+@COLLNAME
		EXEC('ALTER TABLE [' + @TABLE + '] ADD [TEMP_' + @COLNAME + '] ' + @TYPE + ' COLLATE '+@COLLNAME)
		PRINT 'UPDATE [' + @TABLE + '] SET [TEMP_' + @COLNAME + '] = [' + @COLNAME + ']'
		EXEC('UPDATE [' + @TABLE + '] SET [TEMP_' + @COLNAME + '] = [' + @COLNAME + ']')
		PRINT 'ALTER TABLE [' + @TABLE + '] DROP COLUMN [' + @COLNAME + ']'
		EXEC('ALTER TABLE [' + @TABLE + '] DROP COLUMN [' + @COLNAME + ']')
		SET @TABLE = '[' + @TABLE + '].[TEMP_' + @COLNAME + ']' 
		EXEC SP_RENAME @TABLE, @COLNAME, 'COLUMN'
		COMMIT TRANSACTION
	END	
	FETCH NEXT FROM CUR_TABLE INTO @TABLE,@COLNAME,@XTYPE
END
CLOSE CUR_TABLE
DEALLOCATE CUR_TABLE
DROP TABLE #TABLE
GO