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:

Cursor
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.

SELECT name, database_id, user_access, user_access_desc, [state], state_desc
FROM sys.databases;

Here is a cursor stub I like to use:

/*
	Stub code for creating a fast forward cursor
	https://msdn.microsoft.com/en-us/library/ms180169.aspx
*/

/* declare and initialize variables */
DECLARE @variable INT
SET @variable = 0;

DECLARE cur_DoThings CURSOR FAST_FORWARD READ_ONLY FOR 
	--TODO: select statement for what you want to iterate through
OPEN cur_DoThings
FETCH NEXT FROM cur_DoThings INTO @variable

WHILE @@FETCH_STATUS = 0
BEGIN
    --TODO: main part - do something based on record in cursor. Logic goes here.
	FETCH NEXT FROM cur_DoThings INTO @variable
END

CLOSE cur_DoThings
DEALLOCATE cur_DoThings

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

DECLARE @name nvarchar(128), @database_id int, @user_access tinyint, @user_access_desc nvarchar(60), @state tinyint, @state_desc nvarchar(60), @sql NVARCHAR(MAX);
DECLARE curDBs CURSOR FAST_FORWARD READ_ONLY FOR
	SELECT name, database_id, user_access, user_access_desc, [state], state_desc 
	FROM sys.databases
	WHERE database_id > 4
OPEN curDBs
FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @name + ': ' + CAST(@database_id AS VARCHAR);
	
	PRINT 'Taking database offline';
	SELECT @sql = 'ALTER DATABASE ' + @name + ' SET OFFLINE';
	PRINT @sql;
	--EXEC sys.sp_executesql @sql;
    FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc
END
CLOSE curDBs
DEALLOCATE curDBs

The debugging output looks like this:

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

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.

SELECT name, database_id, user_access, user_access_desc, [state], state_desc
FROM sys.databases;

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

DECLARE @name nvarchar(128), @database_id int, @user_access tinyint, @user_access_desc nvarchar(60), @state tinyint, @state_desc nvarchar(60), @sql NVARCHAR(MAX);
DECLARE curDBs CURSOR FAST_FORWARD READ_ONLY FOR
	SELECT name, database_id, user_access, user_access_desc, [state], state_desc 
	FROM sys.databases
	WHERE database_id > 4
OPEN curDBs
FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @name + ': ' + CAST(@database_id AS VARCHAR);
	
	PRINT 'Setting multi user mode';
	SELECT @sql = 'ALTER DATABASE ' + @name + ' SET MULTI_USER';
	PRINT @sql;
	--EXEC sys.sp_executesql @sql;
	
	PRINT 'Setting online';
	SELECT @sql = 'ALTER DATABASE ' + @name + ' SET ONLINE';
	PRINT @sql;
	--EXEC sys.sp_executesql @sql;
    FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc
END
CLOSE curDBs
DEALLOCATE curDBs

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

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

Here is all the code in 1 location:

/**************************************************************************************
	Set databases offline and then back online
	Start with setting all the user databases offline
	Then bring them back online
***************************************************************************************/

USE master;
GO

/*
	Take all user databases offline
*/


SELECT name, database_id, user_access, user_access_desc, [state], state_desc
FROM sys.databases;

DECLARE @name nvarchar(128), @database_id int, @user_access tinyint, @user_access_desc nvarchar(60), @state tinyint, @state_desc nvarchar(60), @sql NVARCHAR(MAX);
DECLARE curDBs CURSOR FAST_FORWARD READ_ONLY FOR
	SELECT name, database_id, user_access, user_access_desc, [state], state_desc 
	FROM sys.databases
	WHERE database_id > 4
OPEN curDBs
FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @name + ': ' + CAST(@database_id AS VARCHAR);
	
	PRINT 'Taking database offline';
	SELECT @sql = 'ALTER DATABASE ' + @name + ' SET OFFLINE';
	PRINT @sql;
	EXEC sys.sp_executesql @sql;
    FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc
END
CLOSE curDBs
DEALLOCATE curDBs


/*
	Bring user databases back online
*/


--Set DBs online and multi user
SELECT name, database_id, user_access, user_access_desc, [state], state_desc
FROM sys.databases;

DECLARE @name nvarchar(128), @database_id int, @user_access tinyint, @user_access_desc nvarchar(60), @state tinyint, @state_desc nvarchar(60), @sql NVARCHAR(MAX);
DECLARE curDBs CURSOR FAST_FORWARD READ_ONLY FOR
	SELECT name, database_id, user_access, user_access_desc, [state], state_desc 
	FROM sys.databases
	WHERE database_id > 4
OPEN curDBs
FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @name + ': ' + CAST(@database_id AS VARCHAR);
	
	PRINT 'Setting multi user mode';
	SELECT @sql = 'ALTER DATABASE ' + @name + ' SET MULTI_USER';
	PRINT @sql;
	EXEC sys.sp_executesql @sql;
	
	PRINT 'Setting online';
	SELECT @sql = 'ALTER DATABASE ' + @name + ' SET ONLINE';
	PRINT @sql;
	EXEC sys.sp_executesql @sql;
    FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc
END
CLOSE curDBs
DEALLOCATE curDBs

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