Anyways, on to what you came to read about.
So the other day I got a ticket. "Hey there are columns in some of these tables in this database that might have a different collation, can you make them all the same?"
Wha....? I don't even want to know. <smh>
Ok, I can do this. So I run this to see how bad this really is (change out the collation throughout as required):
SELECT
c.name as column_name,
o.name as tbl_name,
o.type_desc,
c.collation_name
FROM sys.columns c
join sys.objects o
on c.object_id = o.object_id
where o.type = 'U'
and collation_name <> 'SQL_Latin1_General_CP437_CI_AI'
EEEK! 808! Well crud. So knowing that you have to drop constraints and such before altering a table, I decided to be lazy and see if anyone else has done this before (I mean, manually scripting out foreign keys, indexes, computed columns etc for each table would be a nightmare!). So I found this blog post with scripts to at least get an idea of what we're looking at having to do:
https://www.mssqltips.com/sqlservertip/3221/how-to-change-the-collation-of-a-sql-server-column/
Basically these are scripts to check for objects that need to be dropped. Well that's just great. But then what do I do with this knowledge? I STILL have to drop then re-add all of this stuff.
Have you guessed by now? The answer is POWERSHELL!!
I found that luckily all I had were indexes to contend with, so I threw together a script to grab all of these items. Because Powershell is mega-cool, I'm sure you can modify the script to grab most of the other constraints. Anything that you can get from a SQL query you can feed to Powershell. So on to the next step.
I ran this to get a list of the tables I needed to modify:
Run this to get a list of tables:
DECLARE @collate SYSNAME
SELECT @collate = 'SQL_Latin1_General_CP437_CI_AI'
SELECT distinct
SCHEMA_NAME(o.[schema_id])+ '.' + + o.name + ','
FROM sys.columns c
JOIN sys.objects o ON c.[object_id] = o.[object_id]
JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
AND c.collation_name != @collate
AND o.[type] = 'U'
I put this in a file at C:\tables.csv. Yes I could have done ALL of this in Powershell, but I find it easier to sometimes break things up into chunks. Besides being easier for others to understand, it's easier for ME to understand when I look at what I did a year ago and go huh??
Onward!
In a new query window I run this on the database to get the # of indexes, I use it later to be sure all have been dropped/recreated:
SELECT
so.name AS TableName
, si.name AS IndexName
, si.type_desc AS IndexType
FROM
sys.indexes si
JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE
so.type = 'U' --Only get indexes for User Created Tables
AND si.name IS NOT NULL
ORDER BY
so.name, si.type
Then in another window (yes we'll have a few) I run this to get the Alter Table to change the collation (can't remember where I grabbed this but thank you Internet!):
DECLARE @collate SYSNAME
SELECT @collate = 'SQL_Latin1_General_CP437_CI_AI'
SELECT
'[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name
, 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
ALTER COLUMN [' + c.name + '] ' +
UPPER(t.name) +
CASE WHEN t.name NOT IN ('ntext', 'text')
THEN '(' +
CASE
WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1
THEN CAST(c.max_length / 2 AS VARCHAR(10))
WHEN t.name IN ('char', 'varchar') AND c.max_length != -1
THEN CAST(c.max_length AS VARCHAR(10))
WHEN t.name IN ('nchar', 'nvarchar', 'char', 'varchar') AND c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(10))
END + ')'
ELSE ''
END + ' COLLATE ' + @collate +
CASE WHEN c.is_nullable = 1
THEN ' NULL;'
ELSE ' NOT NULL;'
END
FROM sys.columns c
JOIN sys.objects o ON c.[object_id] = o.[object_id]
JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
AND c.collation_name != @collate
AND o.[type] = 'U'
Save the output to run later.
Now run this, we're getting our drop and create statements, putting them in a folder to run later, be sure to change the variables at the top to what you want else it obviously won't run:
#Scripts out objects with a drop and create script
[System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
$servername = ''
$databasename = ''
$OutputPath = "C:\"
$SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
#set script options for the drop statement
$ScriptDropExisting = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") $SQLServer
$ScriptDropExisting.Options.ScriptDrops = $True
$ScriptDropExisting.Options.IncludeIfNotExists = $True
$ScriptDropExisting.Options.ToFileOnly = $True
#set script options for the create statement
$ScriptCreate = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") $SQLServer
$ScriptCreate.Options.IncludeIfNotExists = $True
$ScriptCreate.Options.ToFileOnly = $True
#$ScriptCreate.Options.AppendToFile = $True
# get the database object
$Database = $SQLServer.Databases.Item($DatabaseName)
#add the database to the output path
$ScriptPath = $OutputPath + [string]$Database.Name + "\"
#get our tables
$tablist = 'C:\tables.csv'
$Tables = @()
$Tables = Import-Csv $tablist -Header 'some'
#loop through each table in the database
ForEach($thing in $Tables)
{write-host $thing.some
$thing = $thing.some
#get the name separated from schema
$TableName = $thing.Substring(($thing.IndexOf("."))+1)
#replace the square brackets
$TableName = $TableName.Replace('[', '')
$TableName = $TableName.Replace(']', '')
#get schema name
$SchemaName = $thing.Substring(0, $thing.IndexOf("."))
$SchemaName = $SChemaName.Replace('[','')
$SChemaName = $SchemaName.Replace(']','')
#Set the table to be the smo object for that table
$Table = $SQLServer.Databases[$DatabaseName].Tables[$TableName, $SchemaName]
#get the indexes on the table
$Objects = $Table.Indexes
if($Objects -ne $null)
{
#does the path exist?
if ((test-path $ScriptPath) -eq $false)
{
New-item -path $ScriptPath -type directory | out-null
}
#loop through each index
ForEach($Object in $Objects)
{
#set the output script file name based on the string we created earlier plus the name of the index we're scripting
$ScriptFileName = $ScriptPath + "\_" + [string]$Object.Name.replace(":", "_") + "_drop.sql"
$ScriptFileName = $ScriptFileName.Replace("<", "")
$ScriptFileName1 = $ScriptPath + "\" + [string]$Object.Name.replace(":", "_") + "_add.sql"
$ScriptFileName1 = $ScriptFileName1.Replace("<", "")
#set the filename in the scripter object
$ScriptDropExisting.Options.Filename = $ScriptFileName
$ScriptCreate.Options.Filename = $ScriptFileName1
#Comfort message
#$Message = [string](get-date) + ": Generating index script for index " + [string]$Object.Name
#write-host $Message
#call the two scripters
$ScriptDropExisting.Script($Object)
$ScriptCreate.Script($Object)
}
}
else
{
write-host "No indexes exist for table :" + [string]$thing " check your list"
}
}
Next (yes I promise we're almost done) we drop the constraints using the SQL we just scripted. Run this, and yes change the parameters at the top to where you're running this:
$TargetDBSQLFILE = Get-Childitem -Path C:\<script path> -Filter *_drop.sql
$servername = ''
$databasename = ''
foreach ($one in $TargetDBSQLFILE )
{#write-host $one.fullname
$file = $one.fullname
# $TargetDBSQLFILE.fullname
echo ""
osql -S $ServerName -d $DatabaseName -E -i $file |Out-Null
write-host "done with $file"
write-host ''}
write-host 'done all together'
Notice the _drop in the filter. This will drop the objects. We will run this again but then be creating the objects.
Run the Alter statements you got from above SQL script to alter the columns to the correct collation.
Run this again be sure to change drop to add. You may also want to change your DB to SIMPLE as it will bloat the log (be sure to change it back afterwards), and obviously set your parameters at the top:
$TargetDBSQLFILE = Get-Childitem -Path C:\<script path> -Filter *_add.sql
$servername = ''
$databasename = ''
foreach ($one in $TargetDBSQLFILE )
{
$file = $one.fullname
echo ""
osql -S $ServerName -d $DatabaseName -E -i $file |Out-Null
write-host "done with $file"
write-host ''}
write-host 'done all together'
Now check your work:
--Run this to get a list of tables with the bad collation:
DECLARE @collate SYSNAME
SELECT @collate = 'SQL_Latin1_General_CP437_CI_AI'
SELECT distinct
SCHEMA_NAME(o.[schema_id])+ '.' + + o.name + ','
FROM sys.columns c
JOIN sys.objects o ON c.[object_id] = o.[object_id]
JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
AND c.collation_name != @collate
AND o.[type] = 'U'
--run this to get the # of indexes, make sure the number matches the one from above:
SELECT
so.name AS TableName
, si.name AS IndexName
, si.type_desc AS IndexType
FROM
sys.indexes si
JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE
so.type = 'U' --Only get indexes for User Created Tables
AND si.name IS NOT NULL
ORDER BY
so.name, si.type
--Last check:
SELECT
c.name as column_name,
o.name as tbl_name,
o.type_desc,
c.collation_name
FROM sys.columns c
join sys.objects o
on c.object_id = o.object_id
where o.type = 'U'
and collation_name <> 'SQL_Latin1_General_CP437_CI_AI'
So I took a ticket that would have taken me HOURS to do and did it in just a few. Hope this helps someone else out!
