Comparing schema by schema name in Visual Studio and SQL Server

Question

I have a SQL Server database with the default schema and a custom schema. So for example two tables might be database.dbo.table1 and database.customschema.table2.

I want to keep the "customschema" under source control, and I have loaded that into my Visual Studio SQL Server Database Project.

When I use the Tools -> SQL Server -> New Schema Comparison... utility, it returns differences for all database schemas.

It would show all the "dbo" tables as being different/deleted in the database project even though I don't intend to keep them in the project under source control.

Is there a way to limit the results to show differences between my database project and the actual database for the "customschema" objects?

Elytron

Answer 1

Yes you can

The trick is to create a special user who could see only that schema. In visual studio use that user account in the connection to database and it should work.

You need to add a new user for your database:

CREATE USER [customschemaUser] FOR LOGIN [customschemaUser] WITH DEFAULT_SCHEMA=[customschema]

You need to create a new role

CREATE ROLE customschemaRole AUTHORIZATION dbo;

and then add the new user to the role

EXEC sp_addrolemember 'customschemaRole', 'customschemaUser';

set the ownership of the schema to the new role

ALTER AUTHORIZATION ON SCHEMA::customschema TO customschemaRole;

now grant permissions on schema for the new user

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
UPDATE, VIEW DEFINITION ON SCHEMA::customschema TO customschemaRole;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO customschemaRole;

In this way when you connect to the database using customschemaUser, you should be able to see only objects in customschema

Concettaconcettina

NOTE: changing the owner of the schema will cause any other user who has direct permissions onto that schema (or objects in that schema) to lose their permissions... ask me how I know.... – Hardheaded

Answer 2

There is a way to achieve this. Perform schema comparision once, on the result page exclude all the unnecessary schemas and leave the ones that you want to compare. Later on save this comparison file (probably add to .gitignore also) and than you can use this file to perform comparisons only for the specific schema cause the file will contain filter settings.

Cytosine

Answer 3

I would say no. The documentation for using Schema Compare explains the options for filtering, which can only exclude types of objects:

You can also click the Options button in the Schema Compare Window toolbar to specify which objects are compared, what types of differences are ignored, and other settings.

ApexSQL also notes this on their page about database comparison (emphasis mine):

Visual Studio Schema Compare can filter object types (but not individual objects) before the comparison, while ApexSQL Diff doesn’t have that option through the Schema compare query window that’s opened in Visual Studio, while it can edit the project afterwards and perform some further narrowing of objects and more detailed filtering

I think if you want to filter out a certain schema you will need to use a different tool. We use Redgate tools (SQL Change Automation, SQL Source Control) and they allow you to define a filter file to exclude certain objects.

Reshape

Answer 4

No, you can't filter out a schema when doing a Schema Comparison on Visual Studio. What you can do is to compare your project (containing only the custom schema) with a database and after the comparison is completed, you can group the differences by schema and once you have the differences group by schema you can Exclude from all the differences that are not grouped under the custom schema.

To group the differences there is a button on the tool header on the schema comparison Group results button And you can exclude groped results by right clicking the group. Exclude selection

Fleck