This is a quick and dirty method I often use when I want to make a change to multiple databases on a SQL Server instance, usually based on a criteria.

It’s a fairly basic level thing to do, and while it is probably trivial to a SQL expert, I find most beginners wouldn’t consider it. So, I thought it was worth sharing.

Let’s say that I have the following set of databases:

I’ve got a set of databases suffixed _dev, and another set suffixed _uat, as well as a couple of others.

Let’s say I need to take the _uat databases offline. I could do that one at a time through the GUI in SSMS. Or I could write SQL statements to do that.

Or, I could write a query to generate the SQL for me. In this case I only have three databases so it’s not going to save me a lot of time, but when you have tens or hundreds…

First, I write a query to make sure I can select out just the databases I want:

SELECT name
FROM sys.databases
WHERE name LIKE '%_uat';

And I view the results:

That’s what I wanted. So, the next stage is to alter that query to generate the SQL statements to take those databases offline. The SQL to set a single database offline is:

ALTER DATABASE [{Database Name}] SET OFFLINE;

So, I alter my query as follows:

SELECT 'ALTER DATABASE [' + name + '] SET OFFLINE;'
FROM sys.databases
WHERE name LIKE '%_uat';

Which gives me:

I can then copy that results into my query window and execute it as a block:

ALTER DATABASE [Users_uat] SET OFFLINE;
ALTER DATABASE [App_uat] SET OFFLINE;
ALTER DATABASE [Config_uat] SET OFFLINE;

Usually I’ll execute the first line initially to make sure I haven’t messed up the syntax and then I’ll run the rest in one go.

If we look at the list of databases again in the Object Explorer you can see it’s done the job:

You can get clever and create a cursor to execute the result-set one at a time using dynamic SQL, but if you’re just performing a one-off task and you want the quickest solution then I find this the best method.

You can use this technique for all sorts of ad-hoc admin tasks, so it’s a very useful little tool to have in your belt.

Also, you’re not just restricted to performing administrative actions on databases, it could be any set of SQL objects. Personally I find doing things using methods like this a lot easier than messing about with PowerShell.

Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.

This is my favourite SSMS trick I’ve discovered recently, probably some time towards the end of last year.

Basically, when you paste in Management Studio, you have not just the option to paste the last thing you selected and copied, but can cycle back through previous things that were in the clipboard. In a quick test I was able to choose from the last 20 clipboard entries.

You can find a menu item for this in the “Edit menu”:

Or as you can see in the menu (and much more easily) you can use Ctrl+Shift+V

Here’s an animation of me using it to cycle through the SQL statements from my last blog post:

This is a great little productivity tool. I was forever copying and pasting one thing, then a second, then having to go back to copy and paste the first thing again. Never again!