Notes About SSDT Comparison Tools

SQL Server Data Tools (SSDT), the component of Visual Studio, has a couple of comparison tools for schema and data comparison. They have some default behavor on saving comparison and choosing a table key. It’s quite obvious how they work but I wanted to test some features.

1. In schema comparison I can uncheck some database objects to exclude them from applying to target database. Then I can save the comparison to file. What will be when I would open the saved comparison whereas the source or target schema has changed?

The question is about whether SSDT saves all database objects names in file or not. The answer is SSDT saves only check/uncheck marks. If you schema is changed, comparison will show you the exact changes, and unchecked items will remain unchecked.

2. Data comparison needs a primary key to see the differences between each table row. It’s reasonable. If you don’t have a primary key, this table won’t be shown for comparison. What else can be used for comparison? Unique key?

In fact, SSDT needs keys in both tables. It can use primary key or unique key, but not the combination of primary key in one table + unique key in another. If you have primary key and unique key on the same table, SSDT will choose primary key. That’s reasonable too, because primary key does not allow null values, it’s a bit more strict than unique key.

Presentation and Demos for SQLSat #458 Moscow Session

sqlsat458_web

Last Saturday, October 17, 2015, I’ve presented a session about developing SQL Server databases with Visual Studio. Here you can download the presentation, demo code and demo solution with some valuable examples of database unit testing.

Presentation (pdf)

Demo code and solution (zip)

Special thanks to Alexander Speshilov for asking a lot of questions and sharing his experience in working with SQL Server Data Tools.

Finding References (Foreign Keys) To This Table

Occasionally I’ve faced with the task to change the value of the primary key, say, in Employee table in SQL Server database. Everything would be OK if this table is not referenced from the other tables. So when I change the value in primary key EmployeeId column, I would break the foreign key constraints.

When you create a table you can specify a REFERENCES … ON UPDATE CASCADE clause, so all changes in primary key would be propagated to the underlying tables (look here for CREATE TABLE syntax and here for referential integrity constraints). But this is not my case, I have the default NO ACTION option. I want to know all tables and columns that are referencing my primary key. This can be done by one of the three ways:

  1. stored procedure

    UPDATE 2016-02-01:
    If a table is not in a dbo schema, add a second parameter for owner.
  2. using system views from sys schema
  3. using INFORMATION_SCHEMA views

These methods work perfectly when you’ve set foreign keys. There may be a situation when you have a logical reference, but it’s not implemented as a constraint. You can try to search for a column name. Maybe you will be lucky if you have strong naming conventions and you include PKĀ column name in FK column name, i.e. primary key is EmployeeId, so the foreign key will be BossEmployeeId or ManagerEmployeeId. If you don’t follow this rule, you might have simple name like ManagerId and it will be more harder to guess where the reference might be.

This query could help you to find tables by column name.

UPDATE 2016-04-13:
Modified version of the above query that include schema name.