How to get difference between sql objects in Azure Sql database vs that created from sqlproj?

Question

Problem: We need to remove dead sql objects(tables,views,stored procs,etc) from Azure sql database which some devs have directly created on the sql db which are not in use and are not part of sql db projects(sqlproj).

So i wanted to know if there is a way to identify those dead sql objects or find out the difference between sql objects in Sql database vs that created from sqlproj ?

Is there a flag in Sql server Data tools which can help achieve this or anything which can get me the difference?

Answer 1

You can deploy using /Action:DeployReport

https://msdn.microsoft.com/library/hh550080(vs.103).aspx#Anchor_5

This will give you a list of objects which:

• Exist on both but are different
• Exist only in the dacpac
• Exist only in the live database

You can also do this using the compare tool inside visual studio (ssdt) or use something like redgate sql compare - if this is a one off I would probably just use that as I prefer the ui but I have a copy of it so can :)

This problem is normally solved by just deploying and anything in the database which isn't in source control is removed. If you always follow the process of deploying from dacpac you can't get in this situation.

It sounds like you probably want to do a one time thing to find out what is different and maybe test those changes somewhere before you drop them.

Everyone working with ssdt has at some point had this exact issue and the only way to fix it is to get either the sqlproj or the database into a known state and keep deploying from dacpac.

ed

Answer 2

You can use the Schema Compare Options available on SQL Server Data Tools (SSDT) as explained here and here.

You can download SSDT for free from here.

Hope this helps.