COMPARE to include collation info ?

• 
  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.

  Chris Auckland July 16, 2010 06:59

  - 0 +
• 

  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.

  philljones22 December 01, 2024 20:28

  - 0 +

Add comment

Please sign in to leave a comment.