How do you (Unit) Test the database schema?

Question

When there are a number of people working on a project, all of who could alter the database schema, what's the simplest way to unit test / test / verify it? The main suggestion we've had so far is to write tests for each table to verify column names, constraints, etc.

Has anyone else done anything similar / simpler? We're using C# with SQL Server, if that makes any real difference.

Updates:

• The segment of the project we're working on is using SSIS packages to do the bulk of the work so there is very little C# code to write unit tests agains.
• The code for creating tables / stored procedures is spread across SQL files. Because of the build system, we could maintain a separate VS DB project file as well, but I'm not sure how that would help us verify the schema either.

Answer 1

One possibly answer is to use Visual Studio for Database developers and keep your schema in source control with the rest of your code. This allows you to see differences and you get a history of who changed what.

Alternatively you could use a tool like SQLCompare to see what has been modified in one database compared to another.

Answer 2

That is an interesting question! There are lots of tools out there for testing stored procedures but not for testing the database schema.

Don't you find that the unit tests written for code generally find any problems with the database schema?

One approach I have used is to write stored procedures to copy test data from the developer's schema to a test schema. This is pretty rough and ready as the stored procedures generally crash when they come across any differences between the schemas but it does alert you to any changes you haven't been told about.

And nominate someone to be the DBA who monitors changes to the schema?

Answer 3

Your (relational) database does two things as far as I'm concerned: 1) Hold data and 2) Hold relations between data.

Holding data is not a behavior so you would not test it

And for ensuring relations just use constraints. Lots of constraints. All over the place.

Answer 4

This does not really fit the unit test paradigm. I would suggest version controlling the schema and limiting write access to a single qualified team member such as the DBA or team lead, who can validate any requested changes against the entire application. Schema changes should not be done haphazardly.

Answer 5

Don't you find that the unit tests written for code generally find any problems with the database schema?

This assumes, of course, that your tests test everything.