Comparison of actual database schemas against each other (within one database)

Question

Hi all,

for a small project I created a database within Azure and one schema for dev and one for prod (to save run cost).

I would like to compare these two schemas now with Azure Data Studio, but it looks like, that I can only compare databases with one another.

Anyone faced this issue or can suggest how to do so?

Thanks in advance

Max

Answer 1

@Maximilian Ksoll Thank you for reaching out.

You can use schema compare extension to achieve the above.

https://learn.microsoft.com/en-us/azure-data-studio/extensions/schema-compare-extension

The Schema Compare extension simplifies the process of comparing databases and gives you full control when synchronizing them - you can selectively filter specific differences and categories of differences before applying any changes. The Schema Compare extension is a reliable tool that saves you time and code.

Please don't forget to mark as accept answer if the reply was helpful.

Regards,

Oury

Answer 2

Hi,

It sounds like you're trying to compare schemas within the same database server, but with different schemas for dev and prod environments. Azure Data Studio does have database comparison capabilities, but to compare schemas within the same server, you might need to adjust your approach slightly.

One effective way to compare schemas in this scenario is to use SQL Server Data Tools (SSDT) or a third-party schema comparison tool. Here's a step-by-step guide using SSDT:

Install SSDT: If you don’t already have it, you can download SQL Server Data Tools from Microsoft’s website.

Create a Schema Comparison Project:

• Open SSDT and create a new SQL Server Database Project.
  • Import your development schema into this project by connecting to your dev database.
    • Create another SQL Server Database Project and import your production schema.
    Perform Schema Comparison:

    - In SSDT, go to the "Schema Compare" feature.
    
       - Set your source schema as the development database project and the target schema as the production database project.
    
          - Run the comparison to view differences and generate a report.
    

Alternatively, tools like Redgate SQL Compare or ApexSQL Diff can also handle schema comparisons and might offer additional features that suit your needs.

Using these tools should help you effectively compare schemas from your dev and prod environments.

Hope this helps!