SQL Comparing DB's
Maybe you have also done this before. You made some great things in a test- or copy database.
And when you are ready to bring it to production, you realize you did not keep track of all the changes you made.
So how do you find out what you changed or added that should be copied to the production database?
Well, there is no 100% fool-proof method. But with these queries you can be pretty sure you will nail it.
What has been changed?
First thing you can check is which objects (Tables, Views, Stored Procedures, Functions and Triggers) are recently modified in your test database.
Good chance that the ones that are on the top of that list are the ones you need to copy to production.
To get a list of what was recently added or modified, you can use sys.objects:
from sys.objects
where type in ('U', 'V', 'P', 'TR', 'FN')
order by modify_date desc
Where U stands for a User Table. V for a View. P is a Stored Procedure. TR is a Trigger and FN is a Function.
You could also do a compare on the modified date from the test and production database like this:
dev.type as DevType,
dev.modify_date as DevModified,
prd.modify_date as PrdModified
from SilverSolutions.sys.objects as dev
left join DBTest.sys.objects as prd on dev.Name = prd.Name collate database_default
where dev.type in ('U', 'V', 'P', 'TR', 'FN')
order by dev.modify_date desc
Here I compared my SilverSolutions database with my DBTest database. Do note that sys is already a schema so you don't need to go for the dbo schema.
Just be sure to change these names in this query to your situation.
Of course you can do a check here where dev.modify_date is not equal to prd.modify_date to filter just the differences.
The differences in detail
Copying the Stored Procedures, Triggers and Functions is not that difficult. Just use the Object Explorer to generate the script to create it.
Then modify that with the correct database in the USE statement on top, and change "create" to "create or alter".
But it can get a bit more tricky when you go for a Table or View.
Maybe you want to see the differences first.
And that goes especially for tables where you need to add or modify the columns in an existing table.
This query will tell you the differences using sys.dm_exec_describe_first_result_set.
declare @PrdDB nvarchar(100)
declare @Object nvarchar(150)
---------------------------------------
-- Specify DB's & Object here:
set @DevDB = 'SilverSolutions'
set @PrdDB = 'edb_SpeekForce'
set @Object = 'Organizations'
---------------------------------------
declare @DevQuery nvarchar(500)
set @DevQuery = 'select top 1 * from ' + @DevDB + '.dbo.' + @Object
declare @PrdQuery nvarchar(500)
set @PrdQuery = 'select top 1 * from ' + @PrdDB + '.dbo.' + @Object
select dev.name as dev_ColumnName,
prd.name as prd_ColumnName,
dev.is_nullable as dev_is_nullable,
prd.is_nullable as prd_is_nullable,
dev.system_type_name as dev_Datatype,
prd.system_type_name as prd_Datatype,
dev.is_identity_column as dev_is_identity,
prd.is_identity_column as prd_is_identity
from sys.dm_exec_describe_first_result_set (@DevQuery, NULL, 0) dev
full outer join sys.dm_exec_describe_first_result_set (@PrdQuery, NULL, 0) prd
on dev.name = prd.name
where
(
isnull(dev.name, '') <> isnull(prd.name, '')
or isnull(dev.is_nullable, 0) <> isnull(prd.is_nullable, 0)
or isnull(dev.system_type_name, '') <> isnull(prd.system_type_name, '')
or isnull(dev.is_identity_column, 0) <> isnull(prd.is_identity_column, 0)
)
GO
In my case I compared 2 completely unrelated databases with the only mutual thing is the Organizations Table.
Not how you would normally use this one, but it will certainly give a lot of differences.
Just look at the results:
Line 1, 8 and 9 show Columns that are present in my Production Database, but not in my Test Database.
Line 13 and up show Columns that are present in my Test Database but not in Production.
Usually, that would be the case and you would need to add those columns to your Production Database.
Line 2 shows that the Name Column is present in both the Test and Production Database.
In the Test Database however, it is a nullable Column which is not the case in the Production Database.
Would this have been a real example of 2 similar databases, it would more likely be the other way round.
It also seems that in the Test Database I changed the number of characters from 50 to 100.
So that would be a change I should also do in the Production Database.
Conclusion
The best way to keep track of changes is to document the changes you make as you go along.
But we all have been there. Sometimes you just forget and realize afterwards you should have done it.
Or you started out thinking it would be just one change but you ended up having done multiple changes.
Or someone else has made changes and you need to find out what someone else did.
Whatever the reason you need to compare databases, these Queries will help you find that out.
You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!