COMPARE to include collation info ?

rwillemain

Perhaps I am looking in the wrong places, but to compare two databases for differences in collations, where can I find this, if possible ?
Thsnk you. Rick

Chris Auckland

Thanks for your post.

SQL Compare will compare the column level collations by default.

When you look at the results grid after a comparison, select the table object from the top grid to view any collation differences in the 'SQL differences' pane at the bottom of the screen.

You can ignore the collations on a column if you wish by checking the project option Ignore > Collations.

SQL Compare won't show you the collation for the entire database as it will only compares at the object level.

I hope this helps.

philljones22

To compare two databases for differences in collations, you can use one of the following methods:

1. Querying System Views in SQL Server

You can query system catalog views such as INFORMATION_SCHEMA.COLUMNS or sys.columns to check the collation for each column in your databases. For example:

-- For Database 1
USE [Database1];
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT NULL;

-- For Database 2
USE [Database2];
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT NULL;

Export these results and compare them using a tool like Excel or a diff utility.

---

2. Third-Party Tools

Tools like SQL Compare (from Redgate) or ApexSQL Diff allow you to compare databases, including schema and collation differences, in a user-friendly interface.

---

3. Custom Script for Automation

Write a custom script in T-SQL or PowerShell that connects to both databases, retrieves collation information, and compares them programmatically.

---

4. Manual Inspection in SSMS

Using SQL Server Management Studio (SSMS), navigate to:

• Database > Tables > Columns
• Right-click a column > Properties > Collation
  While this method works, it can be time-consuming for large databases.

---

To get the more information about latest trends, info and tech visit influencersgone wild.