How to compare tables with unrelated names using the CLI
During database migration, cross-system integration, and data warehouse consolidation, it is common to compare database tables whose names do not match. This often happens when a legacy system uses one naming convention and a new application uses another, or when different teams and vendors apply their own independent naming standards.
Unrelated table names introduce additional complexity into the comparison process. They increase the risk of comparing or synchronizing the wrong tables and make it more difficult to automate repeatable comparison workflows in a reliable way.
This topic describes how to compare database tables with unrelated names by using the dbForge Studio command-line interface and the TableDiff utility.
Prerequisites
- dbForge Studio for SQL Server installed
- TableDiff utility (usually installed with SQL Server)
- Two SQL Server tables with unrelated names and different owners (schemas)
Click to open the SQL script to create the sample tables
-- Drop and recreate SourceDB
IF DB_ID('Dev') IS NOT NULL DROP DATABASE Dev;
CREATE DATABASE Dev;
GO
USE Dev;
GO
-- Create source tables
CREATE TABLE EmployeeList (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
DeptID INT,
Salary MONEY,
HireDate DATE
);
CREATE TABLE DepartmentList (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50),
ManagerID INT,
Location VARCHAR(100),
Budget MONEY
);
-- Insert sample data into EmployeeList (20 rows)
INSERT INTO EmployeeList (EmpID, EmpName, DeptID, Salary, HireDate) VALUES
(1, 'Alice Harris', 1, 31000, '2010-01-16'),
(2, 'Alice Walker', 2, 32000, '2010-01-31'),
(3, 'Bob Anderson', 3, 33000, '2010-02-15'),
(4, 'Bob Hall', 4, 34000, '2010-03-02'),
(5, 'Chris Lewis', 5, 35000, '2010-03-17'),
(6, 'Chris Moore', 6, 36000, '2010-04-01'),
(7, 'Chris Perez', 7, 37000, '2010-04-16'),
(8, 'David Wright', 8, 38000, '2010-05-01'),
(9, 'Elena Allen', 9, 39000, '2010-05-16'),
(10, 'Elena Thompson',10, 40000, '2010-05-31'),
(11, 'Eve Perez', 11, 41000, '2010-06-15'),
(12, 'Eve Thompson', 12, 42000, '2010-06-30'),
(13, 'Fiona Lee', 13, 43000, '2010-07-15'),
(14, 'Frank Johnson', 14, 44000, '2010-07-30'),
(15, 'Frank Perez', 15, 45000, '2010-08-14'),
(16, 'Heidi Harris', 16, 46000, '2010-08-29'),
(17, 'Heidi Lee', 17, 47000, '2010-09-13'),
(18, 'Ivan Jackson', 18, 48000, '2010-09-28'),
(19, 'Ivan White', 19, 49000, '2010-10-13'),
(20, 'Leo Wilson', 20, 50000, '2010-10-28');
-- Insert sample data into DepartmentList (20 rows)
INSERT INTO DepartmentList (DeptID, DeptName, ManagerID, Location, Budget) VALUES
(1, 'Sales', 10, 'New York', 10000),
(2, 'HR', 20, 'Los Angeles', 20000),
(3, 'IT', 30, 'Chicago', 30000),
(4, 'Marketing', 40, 'Houston', 40000),
(5, 'Finance', 50, 'Phoenix', 50000),
(6, 'Legal', 60, 'Philadelphia', 60000),
(7, 'Operations', 70, 'San Antonio', 70000),
(8, 'Logistics', 80, 'San Diego', 80000),
(9, 'Procurement', 90, 'Dallas', 90000),
(10, 'R&D', 100, 'San Jose', 100000),
(11, 'Customer Service', 110, 'Austin', 110000),
(12, 'Security', 120, 'Jacksonville',120000),
(13, 'Admin', 130, 'San Francisco',130000),
(14, 'Quality', 140, 'Columbus', 140000),
(15, 'Engineering', 150, 'Fort Worth', 150000),
(16, 'Distribution', 160, 'Indianapolis',160000),
(17, 'Development', 170, 'Charlotte', 170000),
(18, 'Support', 180, 'Seattle', 180000),
(19, 'Public Relations', 190, 'Denver', 190000),
(20, 'Accounts', 200, 'Washington', 200000);
-- Drop and recreate TargetDB
IF DB_ID('Prod') IS NOT NULL DROP DATABASE Prod;
CREATE DATABASE Prod;
GO
USE Prod;
GO
CREATE SCHEMA Dept;
GO
-- Create target tables
CREATE TABLE Dept.Staff (
StaffID INT PRIMARY KEY,
FullName CHAR(50),
DeptID BIGINT,
AnnualSalary MONEY,
StartDate DATE,
Email VARCHAR(100)
);
CREATE TABLE Dept.Department (
DepartmentID INT PRIMARY KEY,
DeptName CHAR(50),
ManagerStaffID INT,
CityName VARCHAR(100),
BudgetSum DECIMAL,
CreatedDate DATE
);
-- Insert sample data into Dept.Staff (20 rows)
INSERT INTO Dept.Staff (StaffID, FullName, DeptID, AnnualSalary, StartDate, Email) VALUES
(11, 'Eve Perez', 11, 41000, '2010-06-15', '[email protected]'),
(12, 'Eve Thompson', 12, 42000, '2010-06-30', '[email protected]'),
(13, 'Fiona Lee', 13, 43000, '2010-07-15', '[email protected]'),
(14, 'Frank Johnson', 14, 44000, '2010-07-30', '[email protected]'),
(15, 'Frank Perez', 15, 45000, '2010-08-14', '[email protected]'),
(16, 'Heidi Harris', 16, 51000, '2010-09-03', '[email protected]'),
(17, 'Heidi Lee', 17, 52000, '2010-09-18', '[email protected]'),
(18, 'Ivan Jackson', 18, 53000, '2010-10-03', '[email protected]'),
(19, 'Ivan White', 19, 54000, '2010-10-18', '[email protected]'),
(20, 'Leo Wilson', 20, 55000, '2010-11-02', '[email protected]'),
(21, 'Mallory Johnson',21, 51000, '2011-07-30', '[email protected]'),
(22, 'Mike King', 22, 52000, '2011-08-09', '[email protected]'),
(23, 'Mona Anderson', 23, 53000, '2011-08-19', '[email protected]'),
(24, 'Nina King', 24, 54000, '2011-08-29', '[email protected]'),
(25, 'Peggy Martin', 25, 55000, '2011-09-08', '[email protected]'),
(26, 'Peggy Moore', 26, 56000, '2011-09-18', '[email protected]'),
(27, 'Uma Allen', 27, 57000, '2011-09-28', '[email protected]'),
(28, 'Victor Brown', 28, 58000, '2011-10-08', '[email protected]'),
(29, 'Yanni Wright', 29, 59000, '2011-10-18', '[email protected]'),
(30, 'Zara King', 30, 60000, '2011-10-28', '[email protected]');
-- Insert sample data into Dept.Department (20 rows)
INSERT INTO Dept.Department (DepartmentID, DeptName, ManagerStaffID, CityName, BudgetSum, CreatedDate) VALUES
(11, 'Customer Service', 110, 'Austin', 110000, '2020-01-12'),
(12, 'Security', 120, 'Jacksonville', 120000, '2020-01-13'),
(13, 'Admin', 130, 'San Francisco', 130000, '2020-01-14'),
(14, 'Quality', 140, 'Columbus', 140000, '2020-01-15'),
(15, 'Engineering', 150, 'Fort Worth', 150000, '2020-01-16'),
(16, 'Distribution Services',160, 'Indianapolis Downtown', 180000, '2020-01-17'),
(17, 'Dev & Research', 170, 'Charlotte Branch', 170000, '2020-01-18'),
(18, 'Tech Support', 180, 'Seattle Office', 200000, '2020-01-19'),
(19, 'PR & Marketing', 190, 'Denver Office', 195000, '2020-01-20'),
(20, 'Accounting Dept', 200, 'Washington D.C.',210000, '2020-01-21'),
(21, 'Retail', 210, 'Baltimore', 210000, '2020-01-22'),
(22, 'Wholesale', 220, 'Portland', 220000, '2020-01-23'),
(23, 'Manufacturing', 230, 'Nashville', 230000, '2020-01-24'),
(24, 'Design', 240, 'Corpus Christi', 240000, '2020-01-25'),
(25, 'Maintenance', 250, 'Raleigh', 250000, '2020-01-26'),
(26, 'Security Ops', 260, 'Tulsa', 260000, '2020-01-27'),
(27, 'Recruitment', 270, 'Wichita', 270000, '2020-01-28'),
(28, 'Training', 280, 'Cleveland', 280000, '2020-01-29'),
(29, 'Compliance', 290, 'Tampa', 290000, '2020-01-30'),
(30, 'Facilities', 300, 'Aurora', 300000, '2020-01-31');
Compare tables with unrelated names using TableDiff
1. Open Command Prompt.
2. Navigate to the TableDiff utility location.
cd "C:\Program Files\Microsoft SQL Server\150\COM"
3. Run the comparison.
tablediff ^
-sourceserver "your_source_server_name\your_source_instance_name" ^
-sourcedatabase "your_source_database_name" ^
-sourcetable "your_source_table_name" ^
-sourceschema "your_source_schema_name" ^
-destinationserver "your_destination_server_name\your_destination_instance_name" ^
-destinationdatabase "your_destination_database_name" ^
-destinationtable "your_destination_table_name" ^
-destinationschema "your_destination_schema_name" ^
-f "C:\Path\To\Your_Diff_Script.sql"
The operation results in an error, as the tables’ schemas do not match.
TableDiff limitations
TableDiff is a powerful tool for comparing database tables, but it has certain limitations.
| Limitation | Description |
|---|---|
| Identical table structures | Tables must have the same column names, data types, and order. Mismatches can cause errors. |
| Unsupported views | TableDiff does not support comparing views. |
| Column mismatches | Missing or mismatched columns are flagged as discrepancies. |
| Limited data type handling | Complex data types, such as LOBs or custom types, may not be fully supported and could result in errors. |
| Primary key or index mismatches | Differences in primary keys or indexes may not be fully handled. |
| Data comparison | Data comparison is only possible when the table structures are identical. |
Workaround for table name and structure mismatches
A common workaround to address TableDiff limitations is to create a temporary table with the correct schema in the destination database.
1. Since TableDiff does not handle column mismatches and data type differences well, identify the structural differences between the two tables.
SELECT
ISNULL(d.COLUMN_NAME, p.COLUMN_NAME) AS ColumnName,
d.DATA_TYPE AS DevDataType,
d.CHARACTER_MAXIMUM_LENGTH AS DevMaxLength,
p.DATA_TYPE AS ProdDataType,
p.CHARACTER_MAXIMUM_LENGTH AS ProdMaxLength,
CASE
WHEN d.COLUMN_NAME IS NULL THEN 'Missing in Dev'
WHEN p.COLUMN_NAME IS NULL THEN 'Missing in Prod'
WHEN d.DATA_TYPE <> p.DATA_TYPE OR ISNULL(d.CHARACTER_MAXIMUM_LENGTH,-1) <> ISNULL(p.CHARACTER_MAXIMUM_LENGTH,-1)
THEN 'Mismatch'
ELSE 'Match'
END AS Difference
FROM Dev.INFORMATION_SCHEMA.COLUMNS d
FULL OUTER JOIN Prod.INFORMATION_SCHEMA.COLUMNS p
ON d.COLUMN_NAME = p.COLUMN_NAME
WHERE (d.TABLE_NAME = 'DepartmentList' OR d.TABLE_NAME IS NULL)
AND (p.TABLE_NAME = 'Department' OR p.TABLE_NAME IS NULL)
ORDER BY ColumnName;
The query uses a FULL OUTER JOIN to list all columns from both tables, compares their data types and maximum lengths, and displays the result in the Difference column, indicating whether the values match (Match), differ (Mismatch), or are missing from one of the tables (Missing in Dev / Missing in Prod).
2. Create a new temporary table with the same schema as the source table, rename the columns to match the source, and change mismatched data types to match the source.
USE Prod;
DROP TABLE IF EXISTS dbo.Temp_DepartmentList;
SELECT
DepartmentID AS DeptID,
CAST(DeptName AS varchar(50)) AS DeptName,
ManagerStaffID AS ManagerID,
CityName AS Location,
CAST(BudgetSum AS money) AS Budget
INTO Prod.dbo.Temp_DepartmentList
FROM Prod.Dept.Department;
3. Compare the new temporary table with the source table using TableDiff.
Compare tables with unrelated names using dbForge Studio for SQL Server
1. In the top menu, select Comparison > New Data Comparison.
2. Select the source and target connections and databases, then click Next.
3. Configure the comparison options as needed, then click Next.
4. On the Mapping page, click Map Schemas.
5. Map schemas.
Under Source and Target, select schemas to be mapped for comparison, then click Next.
6. Map tables.
Click Map Objects. Select a table under Source objects, then select the corresponding table under Target objects. Click Map, then click OK.
7. Map columns.
In the row with the required pair of tables in the Columns in Comparison column, double-click the field displaying the number of mapped columns. Then click Close.
8. In the Column Mapping dialog, use the dropdown lists to select the columns to map. Then click OK.
9. From the Save drop-down list in the lower-left corner of the wizard, select Save Document. Specify the path to save the data comparison settings, then click Save.
10. From the Save drop-down list, select Save Command Line. Then select the Comparison Project checkbox and specify the path for the file containing the data comparison settings.
11. In the command line display box, remove the /sync switch if you only need to compare tables, and add pause. Click Save, specify the file name and location, and then click Save again.
12. Run the saved .bat file.
Tip
You can automate and schedule database comparison tasks from the command line.
dbForge Studio vs. TableDiff: Feature comparison
| Feature / Capability | TableDiff | dbForge Studio for SQL Server |
|---|---|---|
| Primary purpose | Command-line utility for comparing two tables (data and schema) in SQL Server. | Full IDE for SQL Server development, administration, management, and data/schema comparison. |
| Scope of comparison | Focused on two individual tables at a time, mostly identical structure. | Supports broad comparison/synchronization across schemas, databases, multiple objects (tables, views, routines) and visual mapping. |
| User interface | Command-line only (no GUI) – requires manual scripting and parameters. | Graphical user interface with visual designers, wizards, editors, and mapping tools, plus a command-line interface for scripted workflows. |
| Handling of structural differences | Limited support: tables must have same columns/data types to compare; schema mismatches cause failure. | Stronger support: allows mapping objects/columns, handles schema differences, supports comparison of data and schema with more flexibility. |
| Automation & ease of recurring workflows | Possible via batch files, but each comparison requires specifying full parameters; limited object-set support. | Extensive automation: supports saving projects, command-line generation, scheduling, DevOps integration. |
| Additional toolset beyond comparison | Focused on comparison/sync of tables only. | Full toolkit: SQL coding assistance, debugging, source control, data import/export, monitoring, reporting, data generation. |
| Ease of use for complex scenarios | Challenging or not supported without workarounds (temporary tables, manual alignment). | Designed for these scenarios: supports mapping tables with unrelated names, schema differences, and visual tools to handle complexity. |
| Support and modern UI/UX | Minimal UI, older tool, limited in modernization. | Modern IDE, frequent updates, rich UI/UX, strong user reviews. |