Redgate SQLTest and SQLCop

What is it?

As part of Redgate SQL Test, you get SQLCop (although the tests themselves are freely available to use).
SQLCop is a set of over 100 open-source tests for your databases. The tests were originally maintained by George Mastros but are now managed by Redgate Software.

These tests cover several areas and serve to provide a level of standardisation, uniformity, and security to your database. The areas covered include:

• Code
• Columns
• Tables and Views
• Indexes
• General Configuration
• Health

What’s really cool, is that you can even contribute to the tests yourself here - SQLCop Github.

I find these tests great for standardising database configurations across the board. Additionally, it’s a great introduction to tSQLt, as these tests can be installed and run all from within a GUI with a handful of clicks. It’s a great way to get started writing your own tSQLt tests. Additionally, you can review the tSQLt Intro series we covered here. You have no excuses 🙂

How can we get it?

The first thing we need to do is install the tSQLt framework to our chosen database. Assuming you have Redgate SQL Test installed, open it up within SSMS. If you haven’t installed it into any database yet, you will see this. Hit the ‘Add Database to SQL Test’ option:

You will then simply select the database you wish to add, followed by clicking the ‘Add Database’ button:

You will now get a confirmation of what will happen. As you can see below, several changes will need to be made to your database, such as enabling SQL CLR and turning Trustworthy ON. Additionally, you can see the checkbox to automatically add the SQLCop tests directly to your chosen database:

Once everything is complete, you should see the following screen:

You should now see something like this, which means you’re good to go:

How do we use it

Expanding the SQLCop branch, you will see all the available tests:

(there are many more than displayed on this screen shot!)

I will now run all of the tests against my databases. I currently have these two:

Utilising and accessing the SQLCop tests works the same way as running tests in the previous blog post here.

Right click on the SQLCop branch, followed by Run Tests:

Once we have run them, you will see the outcome of the tests:

Within the available tests, there are some incredibly useful checks that we can run against the databases on our SQL instance. Some of my favourites are:

• Compatibility Level – Checks for any databases that aren’t using the latest compatibility version available for your SQL instance.
• Auto Update Statistics – Checks for any databases that don’t have automatic statistic updates enabled.
• Procedures using dynamic SQL without sp_executesql – Identifies potentially unsafe dynamic SQL calls, such as those using EXEC

Going Forward

SQLCop provides a valuable set of tSQLt-based tests. These tests highlight common database issues and help you enforce best-practice techniques. The tests serve as an excellent starting point for standardising databases within your estate. SQLCop serves as an excellent springboard for adopting the tSQLt framework, allowing your developers and DBAs to gradually explore unit testing in SQL Server and build their own customised tests.

Thanks for reading!