Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Forum Statistics

Users
Members:
1873
Members Online:
0
Guests Online:
116

Total Post History
Posts:
81445
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
BitogClogs

Other

FAQ
All times are UTC [ DST ]

Google Ads

Run SQL Cop test across databases

Please wait...

Run SQL Cop test across databases

Postby nanonerd on Tue Feb 11, 2014 7:34 pm

We are implementing tSQLt as our SQL unit test framework. As such, we are also examining SQL Cop.

We have 8 databases. SQL Cop is nice in that they are prebuilt database checks. If we add tSQLt and SQL Cop to a database, everything works nicely.

However, we would like to separate our SQL Cop tests into a TestAllDatabases database.

QUESTION: Is there a way to run SQL Cop tests from this databases which is able to check all 8 of our databases?

For example, on SQL Server, we might have:

CustomerDB
InventoryDB

TestCustomerDB (holds tSQLt and all unit tests to run against CustomerDB)
TestInventoryDB (holds tSQLt and all unit tests to run against InventoryDB)
TestAllDatabasesDB (holds tSQLt and SQL Cop, but we need SQL Cop to check the other 3 databases. As is, it will only run checks within the TestAllDatabasesDB database).

Here's the code for SQL Cop which checks for store procs that begin with 'sp_%'. Is there a way to modify this code to run the check against all databases on our SQL Server? For example, how do we run this from TestAllDatabases and have it check CustomerDB? Whatever the answer is, we can then repeat this code to check InventoryDB. The idea is to have all the SQL Cop code in one location instead of having to replicate the code within each Testxxx database.


SELECT @Output = @Output + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + Char(13) + Char(10)
From INFORMATION_SCHEMA.ROUTINES
Where SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE 'sp[_]%'
And SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI NOT LIKE '%diagram%'
AND ROUTINE_SCHEMA <> 'tSQLt'
Order By SPECIFIC_SCHEMA,SPECIFIC_NAME
nanonerd
Newbie
Newbie
 
Posts: 3
Joined: Tue Feb 11, 2014 7:32 pm
Unrated

Re: Run SQL Cop test across databases

Postby gmmastros on Tue Feb 11, 2014 7:53 pm

Every SQLCop check returns a single column. As such, you could create a table in your TestAllDatabaseDB that holds the results of the SQL Cop checks.

I would create a table with "TestName", "DatabaseName", TestDateTime, and results.

You could then use sp_msforeachdb to run the sql cop checks. Something like this:

  1. sp_msforeachdb '
  2.  Insert Into TestAllDatabaseDB.dbo.SqlCopResults(TestName, DatabaseName, TestDateTime, Results)
  3.  SELECT ''sp_ check'', ''?'', GetDate(), SPECIFIC_SCHEMA + ''.'' + SPECIFIC_NAME
  4. From [?].INFORMATION_SCHEMA.ROUTINES
  5. Where SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE ''sp[_]%''
  6. And SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI NOT LIKE ''%diagram%''
  7. AND ROUTINE_SCHEMA <> ''tSQLt'''


Please note that I haven't tried or tested this, but the concept is sound.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm
Unrated

Re: Run SQL Cop test across databases

Postby nanonerd on Tue Feb 11, 2014 8:27 pm

Ok, I think I got it. In the FROM line, just change it to CustomerDB.INFORMATION_SCHEMA.ROUTINES ...
nanonerd
Newbie
Newbie
 
Posts: 3
Joined: Tue Feb 11, 2014 7:32 pm
Unrated

Re: Run SQL Cop test across databases

Postby nanonerd on Wed Feb 19, 2014 1:56 am

Hi, thanks. I saw the code on StackOverflow and expanded it to use as a unit test: http://stackoverflow.com/questions/2170 ... -databases

It works great.

Any chance that you have in the works where all of SQL Cop (perhaps a version called SQL Server Cop?) checks all the databases in the server? The main reason is that we have 8 databases on our server. We'd rather not clutter each database with the tSQLt framework + all unit tests within each database given that some of our databases hold up to 200 procs. Instead, it would be ideal if we could just create a TestAllDatabases DB and put all the SQL Cop stuff there. With your modification, I am able to do this for 'test Procedures Named SP_'. But it is difficult trying to convert each one to canvas all the databases like this one.
nanonerd
Newbie
Newbie
 
Posts: 3
Joined: Tue Feb 11, 2014 7:32 pm
Unrated