CODEWITHSUNDEEP
sql-servert-sql
Geoff Dawdy
Geoff Dawdy

Reputation: 906

Compare column collation between two databases

I have a production database and a dev database that apparently have differing collation on one or more columns in certain tables. Is there a way I can script something that will return which columns are different?

Upvotes: 1

Views: 1449

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280439

Red-Gate SQL Compare will highlight collation differences. There are plenty of other alternative tools of various cost and quality, which I blogged about here, but I can vouch for the quality of Red-Gate's tool. If this is a one-time thing you can use the trial version.

That said, if the databases are on the same server, or one of the servers has a linked server to the other, you can do something like this:

SELECT DevObject  = do.name, DevColumn  = dc.name, DevColl  = dc.collation_name,
       ProdObject = po.name, ProdColumn = pc.name, ProdColl = pc.collation_name
FROM devdb.sys.objects AS do
INNER JOIN devdb.sys.schemas AS ds
  ON do.[schema_id] = ds.[schema_id]
INNER JOIN devdb.sys.columns AS dc
  ON do.[object_id] = dc.[object_id]
INNER JOIN productiondb.sys.objects AS po
  ON do.name = po.name
INNER JOIN productiondb.sys.schemas AS ps
  ON ds.name = ps.name
  AND po.[schema_id] = ps.[schema_id]
INNER JOIN productiondb.sys.columns AS pc
  ON dc.name = pc.name
  AND po.[object_id] = pc.[object_id]
WHERE
  dc.collation_name <> pc.collation_name
  -- AND do.name IN (N't1', N't2', N't3', ...) -- filter certain table names
;

If they're on different servers you may need to create a linked server on one and then change the references appropriately to 4-part names, say, linkedservername.devdb.sys.objects etc.

Upvotes: 5

Related Questions