Moreover, from a performance standpoint, even if triggers work with sets instead of rows (unlike UDFs), they fire an additional query (or even more than one). Triggers are evil, according to Glenn Berry ( blog| twitter), because they are a “bad” way to implement referential integrity. If the UDF performs data access, the statement in the scalar function gets invoked for each row, hitting performance badly.
![multiple select and editing with redgate sql toolbelt tips and tricks multiple select and editing with redgate sql toolbelt tips and tricks](https://developer.team/vault/images/2017/09/22/Mpg6.png)
Scalar UDFs are dog-slow because the function gets invoked RBAR (Row-By-Agonizing-Row, for those that don’t know this “ Modenism”). I also read this interesting article by Alexander Kuznetsov ( blog) and some ideas started to flow. Scalar UDFs are dog-slow and also triggers are evil. Somehow, I did not buy the idea that a CHECK with a scalar UDF or a trigger were the only possible solutions. Execute multi-db sql and pass in the actual statementĮXEC sp_executeSQL N nvarchar(max)', days ago I blogged about a weird behaviour of “table-level” CHECK constraints. 'EXEC ' + QUOTENAME( name) + '.sys.sp_executesql ' + ',' + WHERE DATABASEPROPERTY( name, 'IsSingleUser') = 0 System_db = CASE WHEN name IN ( 'master', 'model', 'msdb', 'tempdb') THEN 1 ELSE 0 END Build a statement to execute on each database context Build an intermediate statement that replaces the '?' char * A pattern to use in a LIKE predicate against the database nameĬREATE PROCEDURE nvarchar( nchar( 1) = N nvarchar( 500) = NULL The character to replace with the database name Description: Executes a statement against multiple databases
MULTIPLE SELECT AND EDITING WITH REDGATE SQL TOOLBELT TIPS AND TRICKS CODE
If you like this code and want to use it, feel free to change it to incorporate any kind of filter. I didn’t provide ad-hoc parameters to implement complex filters on sysdatabases, as I’m convinced that they would not be useful enough in a day to day use. This trick can be used as many times as you like, given that you keep on declaring and passing all the parameters you need to the lower levels. While flagged correctly (LOCAL FORWARD_ONLY STATIC READ_ONLY) and run against a temporary table, nevertheless I was a bit disturbed by that tiny little cursor, so I decided to get rid of it.īasically, my code relies on a dynamic SQL pushed down three levels: The main difference with his (and Microsoft’s) implementation is the absence of a cursor. Some months ago, Aaron Bertand ( blog| twitter) came up with a nice replacement and I thought it would be fun to code my own. Let’s face it: it comes handy very often, especially for maintenance tasks.
![multiple select and editing with redgate sql toolbelt tips and tricks multiple select and editing with redgate sql toolbelt tips and tricks](https://www.realwire.com/twitter_writeitfiles/SQL-Search-inside-SQL-OS.jpg)
![multiple select and editing with redgate sql toolbelt tips and tricks multiple select and editing with redgate sql toolbelt tips and tricks](https://www.cathrinewilhelmsen.net/images/redgate/RedgateSQLPromptTabColoringEnvironments01.png)
Though undocumented and unsupported, I’m sure that at least once you happened to use Microsoft’s built-in stored procedure to execute a statement against all databases.