What are the ways to compare and find differences for SQL Server tables and data?

Adamsymonds

Hello Everyone,

I am new to the SQL and doing SQL Server Training.

Sometimes, I need to compare SQL Server tables and/or data to know what has changed. Are there different ways to compare data, datatypes and table structures when using SQL Server.

For example, I have two similar tables in different databases and I want to know what is different. Here is a script that creates sample databases, tables and data.

CREATE DATABASE dbtest01
GO
USE dbtest01
GO

CREATE TABLE [dbo].[example] ([id] [nchar](10) NOT NULL, [type] [nchar](10) NULL, [cost] [nchar](10) NULL,
  CONSTRAINT [Hello] PRIMARY KEY CLUSTERED
  (
  [id] ASC
  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[example]
  VALUES ('001', '1', '40'),
  ('002', '2', '80'),
  ('003', '3', '120')
GO

CREATE DATABASE dbtest02
GO
USE dbtest02
GO

CREATE TABLE [dbo].[example] ([id] [nchar](10) NOT NULL, [type] [nchar](10) NULL, [cost] [nchar](10) NULL,
  CONSTRAINT [Hello] PRIMARY KEY CLUSTERED
  (
  [id] ASC
  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[article]
  VALUES ('001', '1', '40'),
  ('002', '2', '80'),
  ('003', '3', '120'),
  ('004', '4', '160')
GO

Dan_J

Hi @Adamsymonds

Thanks for reaching out to us regarding this!

SQL Compare can be used compare and deploy schema differences between two databases, while SQL Data Compare does the same but at a data level. 

I am including the link to the documentation pages for both products which should help to answer any additional questions you may have: 

SQL Compare: https://documentation.red-gate.com/sc/getting-started
SQL Data Compare: https://documentation.red-gate.com/sdc/getting-started 

I hope this helps!