Unboxing SQL Toolbelt: What is SQL Multi Script for?
SQL Multi Script does one thing and does it well: it will run whatever script or scripts you select, on a list of databases, and collate all the results neatly. However, it has a surprising number of uses. Phil Factor explains how it works and then uses it to search 100 databases, for occurrences of a string, in about 7 seconds.
“…and what does this do?”
Redgate has its star products. When you first get delivery of the toolbelt, you will be excitedly unwrapping the big shiny boxes at the top of the package. “Ooh, a brand-new SQL Compare! Cor! How did you know I wanted SQL Prompt so much?”
Then, finally, after the excited shouts have died away, you’ll find, at the bottom of the box, almost covered in the packing foam, SQL Multi Script. A puzzled look flashes across your face before you break into a forced smile and say something like “Well, I’m sure this might come in useful at some point!“.
Even though I’ve found some of my favorite tools among the packing foam at the bottom of the container, such as SQL Data Generator, even I did not initially get excited about SQL Multi Script. After all, I’m a database developer with a limited requirement to run the same code in a lot of databases on many servers. In fact, I was wrong: it is a useful development and administrative tool that cuts out a lot of work and time.
My main use for the tool is as an adjunct to SSMS. You can do some of the things that SQL Multi Script can do in SSMS, but not nearly as fast or easily. Occasionally, this really matters. This is a very sharp tool that does a different job to SSMS.
What is there to love about SQL Multi Script?
SQL Multi Script isn’t complicated. You have a list of scripts, and you have a whole lot of lists of databases and their servers. SQL Multi Script runs whatever script or scripts that you select, on the list of databases that you select, collects the results, and combines them with the name of the server and database. SQL Multi Script processes in parallel as much as you want it to, and it has been optimized as much as possible. It is very fast.
Why would you run a lot of scripts on a list of databases? I do it to look for tables or routines. I do it to find things that would be of concern, such as potential collation conflicts, heaps, foreign keys without indexes, untrusted constraints: it is a long list. I do it to run a routine on a collection of databases that are set up with different collations and compatibility levels. If you’re also administering a database or server, there is plenty of other information that you need to know. Here are a few examples, from Glen Berry’s famous scripts:
- The size of the database, of Individual Files and the space available
- Log space usage for current database and recovery model.
- The VLF count and status
- The general database properties, such as the default collation, recovery model, log reuse wait description, compatibility level, and whether settings such as Auto Update Statistics Asynchronously, and Delayed Durability are correct.
- I/O Statistics by file
- recent resource usage
- The top waits since last restart
- The most frequently used, slowest and hard-working queries and stored procedures
How does it fit in with the rest of the toolbelt?
I see SQL Multi Script as the ideal way to drill down to investigate a problem caught in SQL Monitor. You need to have a good collection of diagnostic queries, of course, to help.
You might also use it when you need to run a migration script, which will refactor the current schema while preserving the data, on all your development or test databases. However, these scripts would need to be ‘versioned’ as I explain in The Database Development Stage.
SQL Multi Script in action
Here is a screenshot where I’m about to run just a selected script from a library of scripts. You can run a whole series of scripts, but I find that, more generally, I’m just running one:
The application holds some pleasant surprises:
- One of the best is the fact that you can undock the results pane (click
to float the Results pane, then resize it.) so that if you are running a particularly fiendish DMV with lots of columns, you can extend the results pane independently over several screens. - SQL Multi Script can cope with multiple results from the one script and puts them separately into tabs.
- If one script, out of a list of scripts, has an error against one database (it was case-sensitive!) you are alerted with an icon, and you can rapidly drill down to that script on that database, and see the actual error message.
- Results are saved as text or real CSV, (not the so-called ‘comedy-limited’ format of SQL Server) so can easily be read into a database table or Excel.
- The result from each execution is saved for the duration of a session, so you can refer to them
- It combines the results of queries even when they’re structured differently.
- If you want to run server-based scripts, that’s fine. Just select the
masterdatabase only on each of the servers that you want to query.
to float the Results pane, then resize it.) so that if you are running a particularly fiendish DMV with lots of columns, you can extend the results pane independently over several screens.Preparing a script
It is best if the result of a script has the same metadata for each execution, rather than having unique columns for each execution, as you might get from a pivot-table. However, SQL Multi Script copes with the latter. You don’t need to report the database or server because the application adds those columns.
If a script is a one-off, and doesn’t have variables in it, then it doesn’t matter too much how you do it, as long as it performs well and doesn’t block other processes. If, however, it is something you run regularly, then any variables need to be parameterized. This allows the script to be cached. I use sp_executequery to do this.
It is an irritation, because it is difficult to alter a script as a string in any SQL Editor because you lose the highlighting, but well worthwhile: a script for searching databases by keyword took 18 secs to search 104 databases, but this dropped to 7 seconds when the script was parameterized, and therefore cached.
Getting stuck in
Who wouldn’t want to be able to search all their databases for occurrences of a string amongst all those names of tables, stored procedures, views, functions, schemas, columns, parameters and so on? Here is what I run:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
DECLARE @Searchterm NVARCHAR(4000) = 'MySearchString'; --start with the primary object names SELECT Object_Schema_Name(object_id) + '.' + name, Replace(Lower(type_desc), '_', ' ') AS type FROM sys.objects WHERE name LIKE '%' + @Searchterm + '%' AND is_ms_shipped = 0 AND parent_object_id = 0 UNION ALL --now the child object names SELECT Object_Schema_Name(parent.object_id) + '.' + parent.name + '.' + child.name, Replace(Lower(child.type_desc), '_', ' ') AS type FROM sys.objects AS child INNER JOIN sys.objects AS parent ON parent.object_id = child.parent_object_id WHERE child.name LIKE '%' + @Searchterm + '%' AND parent.is_ms_shipped = 0 UNION ALL --let's look at column names SELECT Object_Schema_Name(object_id) + '.' + Object_Name(object_id) + '.' + name, 'column' AS type FROM sys.columns WHERE name LIKE '%' + @Searchterm + '%' AND ObjectProperty(object_id, N'IsSystemTable') = 0 UNION ALL --and index names SELECT Object_Schema_Name(object_id) + '.' + Object_Name(object_id) + '.' + name, 'index' AS type FROM sys.indexes WHERE name LIKE '%' + @Searchterm + '%' AND ObjectProperty(object_id, N'IsSystemTable') = 0 UNION ALL --what about the names of schemas? SELECT name, 'Schema' FROM sys.schemas AS S WHERE name LIKE '%' + @Searchterm + '%' UNION ALL --what about the names of parameters? SELECT Object_Schema_Name(object_id) + '.' + Object_Name(object_id) + '.' + name, 'parameter' AS type FROM sys.parameters WHERE name LIKE '%' + @Searchterm + '%' UNION ALL --and user types. SELECT schemas.name + '.' + T.name, 'User Types' FROM sys.types AS T INNER JOIN sys.schemas ON schemas.schema_id = T.schema_id WHERE T.name LIKE '%' + @Searchterm + '%' AND system_type_id <> user_type_id; |
SQL Multi Script runs whatever scripts that you select against a selected list of databases. Database distribution lists specify a set of databases that SQL Multi Script can execute scripts against. You can have as many of these as you like. I have general lists, lists of servers (with the master database), lists of sample databases at different compatibility levels and collations (for running unit tests), and a few more specialized lists.
To manage distribution lists, click the Configure button in the distribution list pane. The various lists you create are then read in when the application start and populate the drop-down list.
You then just select the list you want to use. SQL Multi Script stores your distribution lists when you exit the application.
OK, now we have a distribution list we select it. Next, we pop our search query into SQL Multi Script, put a suitable search string into the @Searchterm variable, select just the script and then either hit F5 or click Execute Now:
Alternatively, you can right-click on the file in the script list on the left pane and select ‘Execute this script only’, or just select just that file, then hit F5.
In this case, I was searching for the word ‘Abode’.
You’ll get a better performance from this if you parameterize it explicitly by executing it with sp_executequery. Here is the wrapped version:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
DECLARE @TheSearchterm NVARCHAR(4000) = 'Abode'; DECLARE @command NVARCHAR(MAX) SELECT @Command='--start with the primary object names SELECT Object_Schema_Name(object_id) + ''.'' + name as name, Replace(Lower(type_desc), ''_'', '' '') AS type FROM sys.objects WHERE name LIKE ''%'' + @Searchterm + ''%'' AND is_ms_shipped = 0 AND parent_object_id = 0 UNION ALL --now the child object names SELECT Object_Schema_Name(parent.object_id) + ''.'' + parent.name + ''.'' + child.name, Replace(Lower(child.type_desc), ''_'', '' '') AS type FROM sys.objects AS child INNER JOIN sys.objects AS parent ON parent.object_id = child.parent_object_id WHERE child.name LIKE ''%'' + @Searchterm + ''%'' AND parent.is_ms_shipped = 0 UNION ALL --let''s look at column names SELECT Object_Schema_Name(object_id) + ''.'' + Object_Name(object_id) + ''.'' + name, ''column'' AS type FROM sys.columns WHERE name LIKE ''%'' + @Searchterm + ''%'' AND ObjectProperty(object_id, N''IsSystemTable'') = 0 UNION ALL --and index names SELECT Object_Schema_Name(object_id) + ''.'' + Object_Name(object_id) + ''.'' + name, ''index'' AS type FROM sys.indexes WHERE name LIKE ''%'' + @Searchterm + ''%'' AND ObjectProperty(object_id, N''IsSystemTable'') = 0 UNION ALL --what about the names of schemas? SELECT name, ''Schema'' FROM sys.schemas AS S WHERE name LIKE ''%'' + @Searchterm + ''%'' UNION ALL --what about the names of parameters? SELECT Object_Schema_Name(object_id) + ''.'' + Object_Name(object_id) + ''.'' + name, ''parameter'' AS type FROM sys.parameters WHERE name LIKE ''%'' + @Searchterm + ''%'' UNION ALL --and user types. SELECT schemas.name + ''.'' + T.name, ''User Types'' FROM sys.types AS T INNER JOIN sys.schemas ON schemas.schema_id = T.schema_id WHERE T.name LIKE ''%'' + @Searchterm + ''%'' AND system_type_id <> user_type_id;' EXECUTE sys.sp_executesql @Command,N'@Searchterm nvarchar(4000)', @Searchterm=@TheSearchterm |
Conclusions
SQL Multi Script, by itself, isn’t exciting. However, when you combine it with an arsenal of useful scripts then suddenly, it becomes a very useful tool. It encourages the collecting instinct for useful queries, and what better place to start than Glen Berry’s SQL Server Diagnostic Information Queries. It is easy to start hoarding once you have the tool because you now have the gun, and just need the silver bullets.
As I’m forever forgetting to delete utility procedures from databases and forgetting where I had kept routines. I find a search routine useful. The one I provide is easily tweaked to meet your requirements. There are plenty of other queries that provide another use for Multi Script. In my next article, I show an example of Gathering SQL Server Performance Counters for Multiple Servers.
One of the reasons I like Multi Script so much is that it just does one thing and does it well. However, there are one or two things I want that aren’t there: basically, with a Command-line interface (CLI), it would just fill in the slight tiresomeness of having a ‘manual’ process as part of an automated one. However, I wouldn’t want otherwise to spoil the inherent simplicity of the tool.
Tools in this post
SQL Toolbelt Essentials
The industry-standard tools for SQL Server development and deployment.
You may also like
-
Live training session
Level up with SQL Toolbelt Essentials: Standardizing development with SQL Prompt and SQL Toolbelt Essentials
In this session, get an overview and an introduction into each of the 10 tools included in SQL Toolbelt Essentials as well as discovering the enhanced SQL Prompt features specifically for SQL Toolbelt Essentials, which include The Platform and Bulk Actions. You'll also learn how you can standardize and scale development practices across teams and pave the way for automation with version control.
-
Article
Sharing SQL Prompt Code Snippets and Styles via the Redgate Platform
SQL Prompt users can now share formatting style or code snippets in designated Team spaces on the Redgate Platform. Louis Davidson explains how it works.
-
Article
What is the SPACE developer productivity framework?
The SPACE framework is an acronym for an approach to measuring, understanding, and optimizing engineering productivity. Outlined by researchers from GitHub, Microsoft, and the University of Victoria, it encourages leaders to look at productivity holistically, placing leading metrics in context with each other and linking them to output, often team goals, rather than individual effort.
-
Article
How to document multiple SQL Server databases using SQL Doc and PowerShell
Phil shows how to use SQL Doc, PowerShell and a SQL Doc XML Project file to document multiple databases at once.
-
Forums
SQL Toolbelt Essentials Forum
Discussions on Redgate's SQL Server products including SQL Prompt, SQL Monitor and SQL Change Automation.
