CREATE PROCEDURE SP_TW_TO_PRC
@TypeName VARCHAR(255)
,@OldCollation VARCHAR(255)
,@NewCollation VARCHAR(255)
AS
SET NOCOUNT ON
DECLARE @TmpTab TABLE
(
SID INT IDENTITY(1,1)
,TabName VARCHAR(255)
,ColName VARCHAR(255)
,[PRECISION] VARCHAR(10)
)
INSERT INTO @TmpTab(TabName, ColName, [PRECISION])
SELECT OBJECT_NAME(id) as TabName ,NAME as ColName, prec FROM syscolumns
WHERE id IN
(
SELECT id FROM sysobjects WHERE xtype='u' AND name <> 'dtproperties'
)
AND xtype IN
(
SELECT xtype FROM systypes
WHERE name = @TypeName
)
AND collation = @OldCollation
ORDER BY id, colid
DECLARE @MaxCount INT
,@SID INT
,@strSQL VARCHAR(8000)
SELECT @SID = 1, @MaxCount = MAX(SID) FROM @TmpTab
WHILE @SID <= @MaxCount BEGIN
SELECT @strSQL = 'ALTER TABLE ' + TabName
+ ' ALTER COLUMN ' + ColName
+ ' ' + @TypeName +
CASE @TypeName
WHEN 'text' THEN ''
WHEN 'ntext' THEN ''
ELSE '(' + [PRECISION] + ')'
END
+ ' COLLATE ' + @NewCollation
FROM @TmpTab
WHERE SID = @SID
PRINT @strSQL
EXEC(@strSQL)
SET @SID = @SID + 1
END
RETURN 0
go
SP_TW_TO_PRC 'varchar','Chinese_Taiwan_Stroke_CI_AS','Chinese_PRC_CI_AS'
go
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='SP_TW_TO_PRC' AND XTYPE='p')
DROP PROCEDURE SP_TW_TO_PRC
@TypeName VARCHAR(255)
,@OldCollation VARCHAR(255)
,@NewCollation VARCHAR(255)
AS
SET NOCOUNT ON
DECLARE @TmpTab TABLE
(
SID INT IDENTITY(1,1)
,TabName VARCHAR(255)
,ColName VARCHAR(255)
,[PRECISION] VARCHAR(10)
)
INSERT INTO @TmpTab(TabName, ColName, [PRECISION])
SELECT OBJECT_NAME(id) as TabName ,NAME as ColName, prec FROM syscolumns
WHERE id IN
(
SELECT id FROM sysobjects WHERE xtype='u' AND name <> 'dtproperties'
)
AND xtype IN
(
SELECT xtype FROM systypes
WHERE name = @TypeName
)
AND collation = @OldCollation
ORDER BY id, colid
DECLARE @MaxCount INT
,@SID INT
,@strSQL VARCHAR(8000)
SELECT @SID = 1, @MaxCount = MAX(SID) FROM @TmpTab
WHILE @SID <= @MaxCount BEGIN
SELECT @strSQL = 'ALTER TABLE ' + TabName
+ ' ALTER COLUMN ' + ColName
+ ' ' + @TypeName +
CASE @TypeName
WHEN 'text' THEN ''
WHEN 'ntext' THEN ''
ELSE '(' + [PRECISION] + ')'
END
+ ' COLLATE ' + @NewCollation
FROM @TmpTab
WHERE SID = @SID
PRINT @strSQL
EXEC(@strSQL)
SET @SID = @SID + 1
END
RETURN 0
go
SP_TW_TO_PRC 'varchar','Chinese_Taiwan_Stroke_CI_AS','Chinese_PRC_CI_AS'
go
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='SP_TW_TO_PRC' AND XTYPE='p')
DROP PROCEDURE SP_TW_TO_PRC