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.
- 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.
- Import your development schema into this project by connecting to your dev database.
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!