Hi Nina,
I've had a crack at this and it seems possible - but a bit of effort is required to come up with a series of scripts to do this.
I've spent half an hour on it so far but I think only another 30 minutes to an hour would be required to finish it off - but then there is testing to make sure the resultant database works.
The following DOES NOT work, and I've added comments of where I got to - not much use to you, I know - but I wanted to show some effort and progress has been made - I'm going to leave it where it is and come back to it - but you're more than welcome to continue on with it yourself or if it's critical to you then we can expedite the work through a billable service issue.
Mike
Backup your database first.
Firstly, there is a function which will need to be dropped and then re-created later:
- Code: Select all
DROP FUNCTION [fn_split]
Then you need to put the database into single user mode:
In the below replace JiwaDemo with your database name. Make sure you run this on the master database, and no other user can have an open connection to the database - not even SQL Management studio.
- Code: Select all
ALTER DATABASE JiwaDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then you need to change the default collation for the database - run this against the master database and again replace JiwaDemo with your database name :
- Code: Select all
ALTER DATABASE JiwaDemo COLLATE SQL_Latin1_General_CP1_CI_AS
Now a script needs to be run to ALTER every column of every table with the new collation - the below script outputs text which you need to run against the database:
**** NOTE: The output when run currently fails due to foreign keys - they will need to be dropped before running and re-created after altering the collation - I suspect indexes as well will need attention- Code: Select all
DECLARE @TableName VARCHAR(500)
DECLARE @CollationName VARCHAR(500)
DECLARE @ColumnName VARCHAR(500)
DECLARE @SQLText VARCHAR(MAX)
DECLARE @DataType VARCHAR(500)
DECLARE @CharacterMaxLen INT
DECLARE @IsNullable VARCHAR(50)
SET @CollationName = 'SQL_Latin1_General_CP1_CI_AS'
DECLARE MyTableCursor CURSOR FOR SELECT name FROM sysobjects
WHERE type = 'U'
ORDER BY name
OPEN MyTableCursor
FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE MyColumnCursor Cursor
FOR
SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE from information_schema.columns
WHERE table_name = @TableName AND (Data_Type LIKE '%char%'
OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
ORDER BY ordinal_position
Open MyColumnCursor
FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
@CharacterMaxLen, @IsNullable
WHILE @@FETCH_STATUS = 0
BEGIN
IF @CharacterMaxLen = -1 OR @CharacterMaxLen > 8000
BEGIN
SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' +
'TEXT' +
' COLLATE ' + @CollationName + ' ' +
CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
END
ELSE
SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' +
@DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' WHEN @CharacterMaxLen >= 8000 THEN 'MAX' ELSE CAST(@CharacterMaxLen AS VARCHAR(50)) END +
') COLLATE ' + @CollationName + ' ' +
CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
PRINT @SQLText
FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
@CharacterMaxLen, @IsNullable
END
CLOSE MyColumnCursor
DEALLOCATE MyColumnCursor
FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor
Then set the database back to multi-user mode - run this against the master database, and again replace JiwaDemo with your database name.:
- Code: Select all
ALTER DATABASE JiwaDemo SET MULTI_USER WITH ROLLBACK IMMEDIATE
The recreate the fn_split function we deleted earlier (make sure you run this against your database you have changed the collation on):
- Code: Select all
CREATE FUNCTION [dbo].[fn_split](@sText varchar(8000), @sDelim varchar(20) = '')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx int,
@value varchar(8000),
@bcontinue bit,
@iStrike int,
@iDelimlength int
IF @sDelim = 'Space'BEGIN
SET @sDelim = ' '
END
SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1
IF(Len(@sText) = 0)
RETURN
IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty')) BEGIN
WHILE @bcontinue = 1
BEGIN
--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
IF CHARINDEX(@sDelim, @sText)>0 BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
END
ELSE BEGIN
--If you can't find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE BEGIN
WHILE @bcontinue=1 BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1 BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
END
ELSE BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END
END
RETURN
END
GO
Then grant permissions:
- Code: Select all
GRANT_ALL_USER_TABLES