T-SQL Tuesday Logo

T-SQL Tuesday #104 – Code You Would Hate to Live Without

The topic for this months T-SQL Tuesday #104 (hosted by Bert Wagner Blog/Twitter) is:

“code you’ve written that you would hate to live without.”

I will share with you a simple snippet that I have used over the years and look forward to reading everyone’s code!

The Scenario

Sometimes we need to loop through all the user databases and take some action. For example – moving database files or migrating databases. The basic logic can be used to loop through a list of databases on a SQL instance and perform some kind of operation on them sequentially.

The Implementation

This is not meant to be wildly original or fancy or complex but rather something mundane that I keep using over the years to do my job.

In this example I will show taking databases offline and back online. The 2 components of this are:

  1. Cursor
  2. Dynamic SQL

They can be paired well to accomplish many DBA tasks. Sometimes we need to do something row by row instead of set based and also need dynamic SQL for run-time variables. Let us now walk through the code.

We start with taking a look at the databases on our SQL Instance. Take note of the database IDs because we use this later the determine system vs user database.

List of databases

Here is a cursor stub I like to use:

Now let’s see it in action. We are going to first look at the debugging prints before executing.

The debugging output looks like this:

Cursor to set all user databases offline

Now we un-comment the call to sp_executesql and the work is done. Here is the state of the databases now:

Offline databases

Looking good so far. Now let’s use the same snippet and logic to return the databases to be online.

Running this again shows us which databases are online and offline. Note that the system databases are still online and only the user database have been taken down.

Database status

This is the snippet of code to bring them back online.

Here is the debugging output from the above – it is the cursor generated dynamic SQL to bring the databases back online.

Cursor generated dynamic sql to bring dbs online

Once again un-comment the call to sp_executesql and the work is done.

Databases are back online

Here is all the code in 1 location:

Hopefully this helps you make the job easier! Simply substitute out the dynamic SQL and adjust the cursor definition to do the job you need to do.

Thanks for reading!


If you liked this post then you might also like: Migration – How to Move SQL Server Database Files to a New Location

Did you find this helpful? Please subscribe!

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

One thought on “T-SQL Tuesday #104 – Code You Would Hate to Live Without

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.