PaulB-TheOneAndOnly (3/11/2011)
Out of curiosity, may I ask what kind of issues?Backup/Restore and Detach/Attach methods are not supposed to change collation on target system e.g. collation remains the same as the one in source system.
I don't completely understand collations, to be honest. And unfortunately we don't have a DBA where I work to help guides us through things like this. The problems began when the developers all received new workstations. The local SQL Server install used a different collation than our staging server. I don't specifically remember what the issues were, just error messages when running stored procedures. Some tables had a specific collation applied to them that was different than the database collation. Its all quite messed up to be honest. But we found that as long as we use scripts to generate the database we didn't encounter the issues.
I wrote the following script to modify the collation for each table in a database, but didn't have the confidence in its implications to ever run it anywhere but on my local DB.
DECLARE @DB_Collation nvarchar(50)
SET @DB_Collation = CAST(DATABASEPROPERTYEX('TargetDbName', 'Collation') as nvarchar)
-- Display the rows before the updated
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE (
COLLATION_NAME IS NOT NULL AND
COLLATION_NAME != @DB_Collation AND
EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE')
)
DECLARE dynamicSqlCursor CURSOR
FOR SELECT 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + char(10) +
'ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
CASE WHEN DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar'
THEN '(' +
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1
THEN 'MAX'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH as varchar)
END + ')'
END + char(10) + 'COLLATE ' + @DB_Collation + char(10) +
CASE IS_NULLABLE
WHEN 'NO' THEN 'NOT NULL'
WHEN 'YES' THEN 'NULL'
END + char(10)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE (
TABLE_SCHEMA != 'dbo' AND -- Avoid system tables
COLLATION_NAME IS NOT NULL AND -- Null collation will fall back on database collation anyways
COLLATION_NAME != @DB_Collation AND -- This is our true target: old collation values
EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE') -- Alter tables only
)
DECLARE @SQL nvarchar(MAX)
OPEN dynamicSqlCursor
FETCH NEXT FROM dynamicSqlCursor
INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @SQL
-- Execute the query
EXEC sp_executeSql @SQL
FETCH NEXT FROM dynamicSqlCursor
INTO @SQL
END
CLOSE dynamicSqlCursor
DEALLOCATE dynamicSqlCursor
-- Verify the results. The following query should return 0 rows.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE (
COLLATION_NAME IS NOT NULL AND
COLLATION_NAME != 'SQL_Latin1_General_CP1_CI_AS' AND
EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE')
)