Description
The main use case for this is in order to "spy" system stored procedures.
For example, I am currently writing tests for a procedure which calls sp_send_dbmail. I don't want to actually send the email; in fact there isn't a dbmail profile setup. But I do want to assert that my procedure a) is calling the proc the correct number of times, and b) is calling it with the correct parameters.
Procedures which schedule SQL Agent jobs are another good example.
I think theoretically you could get around this by deploying tSQLt to the system database, and calling [msdb].[tSQLt].[SpyProcedure], but the idea of deploying code to a system database makes my skin crawl. It's ok to spy procedures in the system database, because this happens in a transaction, and gets rolled back nice and cleanly. But uninstalling tSQLt from the database might not be as clean.
Another scenario where this would help is when your proc calls out to another database. Currently, you would have to deploy tSQLt to that database as well, and call [OtherDb].[tSQLt].[SpyProcedure]. This isn't as much of a problem, but it's a little frustrating having to deploy tSQLt to another database just to spy a single proc.
I think I should be able to code these changes; I've worked around this in my tests. I'm just raising this a) to have something to relate the pull request to, and b) to make sure people agree that it is a desired feature, and I'm not contravening some part of tSQLt's design principle.