February 7, 2013 at 7:59 am
OK,before trying to reinvent the wheel.
Anyone got a script that
a)identifies columns that ou can change the collate on
b)detect and drop indexes ect... on these
c)change collate
d) recreate the indexes
February 7, 2013 at 5:32 pm
I have no script, but this will probably be faster than pure ALTER COLUMN method on very large tables:
1) Use SELECT * FROM INFORMATION_SCHEMA.COLUMNS to find the tables and column you want to change collation.
2) Use BCP OUT to export all the rows to the files. Use -w option to get textual fields encoded with unicode in the files, not to lose data.
3) Script non-clustered indexes (included columns, filegroups, partitioning and other index properties), then drop them
4) Truncate those tables
5) Alter collation - very fast because tables are empty
6) Use BCP IN to import data from files back to tables. Now they sit into new collation. Check documentation for conditions that operation is minimally logged. (use TABLOCK and ORDER special BCP hints)
7) Recreate nonclustered indexes you've dropped
That would be the steps in your script.
Before running the script:
Backup your database (just in case). Take transaction log backup and after that put db in bulk-logged recovery model, and optionally in restricted or single-user mode.
After running the script:
Revert to full recovery model, multi-user, and take transaction log backup.
Test the script, of course.
Review possible loss of characters with this query (change collation names, of course):
-- codepage differences - useful to detect conversion losses!
SELECT Code = t.number,
Croatian = CHAR(t.number) COLLATE Croatian_CI_AS,
Latin1 = CHAR(t.number) COLLATE Latin1_General_CI_AS
FROM master.dbo.spt_values t
WHERE t.type='p'
AND t.number BETWEEN 0 AND 255
AND UNICODE(CHAR(t.number) COLLATE Croatian_CI_AS) <> UNICODE(CHAR(t.number) COLLATE Latin1_General_CI_AS)
If your collations have different sets of characters, you will see them here.
Here is BCP out and BCP in script example:
http://www.nigelrivett.net/SQLTsql/BCPAllTables.html
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply