Decoding Database Discrepancies: How to compare table structures across different databases
Did you know that data migrates at a particular time like birds do during the cold season? However, data migration occurs much less frequently than bird migration. When the time for migration comes, it is important to pay close attention to the database structure. Imagine what would happen if records from a table disappeared without a trace — cue the alarms! In the world of data migration, the stakes are high, and understanding the intricacies of database structures becomes crucial.
To prevent any panic during the migration process we’ll discuss the importance of checking the table structure from different databases before migration, and how to do it in SSMS. By doing so, you can ensure that no data escapes and that every piece finds its rightful place within your table.
So, arm yourself with knowledge, be prepared, and safeguard your data — its home is on your table, not elsewhere.
Why does everyone migrate?
In some cases such as system upgrades or replacements, move to the cloud, data center relocation, and so on, the migration process is more than needed and cannot be avoided; and after all why should we be afraid of it?
According to the textbooks,
Data migration is the process of transferring data from one location or format to another. This could involve moving data between different computer systems, storage types, or file formats.
Looking at the definition, you might think it’s an easy-peasy thing to do, but I tell you it isn’t. However that’s a story for another article (stay tuned😉)
Get everything ready for our data trip
Let’s think about how much preparation goes into your long-awaited trip; packing your bags, finding a hotel, booking flight tickets and rooms —comment below if I missed any essential steps 😄. Migrating data is no different; you plan to move it, but where to?
This is where Schema Analysis/ Schema Assessment comes to our rescue; we can think of it as a tourist guide that can help us by giving us different information about our journey — places to explore, things to see, and more.
When it comes to data “trip”, we can uncover differences in table structures, primary and foreign key relationships, constraints, columns data type, indexes, and much more. However, today’s primary point of interest lies in exploring the nuances of table structure.
But what a table analysis is?
A table structure examination involves a meticulous analysis of the tables within a database and represents the crucial step in the data migration process. It provides a comprehensive understanding of the structure and characteristics of these tables. Delving deeper, this examination involves different steps, such as
- Column analysis — understand each table’s columns, including their names, data types, and sizes. This information aids in mapping columns from the source to the target database.
- Primary key analysis — identify the primary key(s) of each table. Note that you need to understand and maintain them to preserve data integrity.
- Foreign key relationships — check if your table is in a relationship with other tables because they need to be replicated in the target database.
- Check constraints — examine constraints that may involve conditions data must meet.
- Index analysis — analyze the existence of indexes and consider how they will be replicated in the target database for optimal query performance.
- Size of columns — this information is important for planning the migration process, taking into account performance and resource requirements.
How to do it?
Not too long ago i had to do a migration on a client’s machine, ruling out the possibility of a comparison using Visual Studio — unfortunate, isn’t it? 🥲. What to do, what to do? Well, i happen to be a bit lazy, so I had to come up with a solution for an easy and quick comparison.
To solve this, i created a stored procedure equipped with 4 parameters, such as source_database_name, source_schema_name, source_table_name, target_database_name, target_schema_name, target_table_name (you can reduce the parameters if the schema and/or table share the same name).
CREATE PROC [dbo].[Find_Differences_Between_Tables] @source_database_name VARCHAR(100), @source_schema_name VARCHAR(100),
@source_table_name VARCHAR(200), @target_database_name VARCHAR(100), @target_schema_name VARCHAR(100), @target_table_name VARCHAR(200)
AS
BEGIN
CREATE TABLE #TempResult (ColumnName NVARCHAR(MAX), IsNullable NVARCHAR(10), DataType NVARCHAR(50), ColumnsLength INT);
-- Declare variables
DECLARE @Sql NVARCHAR(MAX);
-- Your dynamic SQL query
SET @Sql = '
SELECT COLUMN_NAME, is_nullable, data_type, character_maximum_length
FROM ' + @target_database_name + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ' + CHAR(39) + @target_table_name + CHAR(39) +' and TABLE_SCHEMA = ' + CHAR(39) + @target_schema_name + CHAR(39) +
' EXCEPT
SELECT COLUMN_NAME, is_nullable, data_type, character_maximum_length
FROM ' + @source_database_name + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ' + CHAR(39) + @source_table_name + CHAR(39) + ' and TABLE_SCHEMA =' + CHAR(39) + @source_schema_name + CHAR(39) + '';
-- Insert the results into the temporary table
EXEC sp_executesql @Sql;
INSERT INTO #TempResult (ColumnName, IsNullable, DataType, ColumnsLength);
-- Retrieve records from the temporary table (these records are the differences between the 2 tables)
SELECT * FROM #TempResult;
-- Delete the temporary table
DROP TABLE #TempResult;
end;How is it working?
After writing the syntax for creating a stored procedure, i established a temporary table that serves as a repository for all information about the differences between the 2 tables involved in this comparison process.
CREATE TABLE #TempResult
(
ColumnName NVARCHAR(MAX),
IsNullable NVARCHAR(10),
DataType NVARCHAR(50),
ColumnsLength INT
);I used Dynamic SQL so i had to declare a variable (i named it @sql) to store the SELECT statement executed on INFORMATION_SCHEMA.COLUMNS view. From there, i extracted columns containing the following information: the name of the table column, data type, and whether the columns can contain NULL values, along with their length.
DECLARE @Sql NVARCHAR(MAX);
-- Your dynamic SQL query
SET @Sql = '
SELECT column_name, is_nullable, data_type, character_maximum_length
FROM ' + @target_database_name + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ' + CHAR(39) + @target_table_name + CHAR(39) +' and TABLE_SCHEMA = ' + CHAR(39) + @target_schema_name + CHAR(39) +
' EXCEPT
SELECT column_name, is_nullable, data_type, character_maximum_length
FROM ' + @source_database_name + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ' + CHAR(39) + @source_table_name + CHAR(39) + ' and TABLE_SCHEMA =' + CHAR(39) + @source_schema_name + CHAR(39) + '';After that, i executed the SELECT statement and stored the returned rows in the temporary table i created in the first step.
-- Insert the results into the temporary table
EXEC sp_executesql @Sql;
INSERT INTO #TempResult (ColumnName, IsNullable, DataType, ColumnsLength);In the end, i performed another SELECT, this time on the temporary table, and dropped it after retrieving the information.
SELECT * FROM #TempResult;
-- Delete the temporary table
DROP TABLE #TempResult;
end;See it in action
I have the ‘Employee’ table name and ‘dbo’ schema in both the source and target database. The target database is ‘New_db’, and the source database is ‘Old_db’.
In the ‘Employee’ table from the source database ‘Old_db’ i have the following structure:
On the other hand, in the target database (‘New_db’), the structure is as follows:
Since both tables have fewer columns, the difference is apparent at a glance. However, for the sake of simplicity, I’ve chosen this example 😄
If we execute the stored procedure, the result will be as follows:
Upon analyzing the table creation statements, it was discovered that the target table has more columns than the source table. Specifically, the address, city, state, and managerID columns were added, along with a modified position column. In the source table, the position column accepted NULL values, but in the target table, it no longer does, which is why it appears in the result returned by the stored procedure.
And that’s the whole magic 😁
Conclusion
While a Schema Comparison done with Visual Studio might be faster than this store procedure, there are situations when alternative approaches become necessary. The crucial point is to conduct a meticulous analysis to mitigate the risk of losing vital information during migration.
Don’t forget: Take the necessary steps to ensure a smooth and successful transfer.
Thank you for taking the time to explore my article. If you found the information valuable and would like to connect further, I invite you to connect with me on LinkedIn. For more visit My Medium and embark on an exciting journey of discovery.
P.S: If you found this article interesting and helpful, you have the option to support my work here ☕😊
