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;

1 2	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

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

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;

1 2	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

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

/**************************************************************************************

Set databases offline and then back online

Start with setting all the user databases offline

Then bring them back online

***************************************************************************************/

USE master;

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

Did you find this helpful? Please subscribe!

5 thoughts on “T-SQL Tuesday #104 – Code You Would Hate to Live Without”

T-SQL Tuesday #104 Roundup – SQL with Bert says:

July 17, 2018 at 6:01 am

[…] Jeff Mlakar offers a solution for taking all databases on an instance offline (and then back online) again. […]

SQL Server Database Corruption! Understanding and Diagnosing – MlakarTechTalk says:

July 20, 2018 at 7:33 am

[…] can run this programmatically over your databases using the techniques I laid out […]

How to Insert Into a Table Using Excel to Write SQL – MlakarTechTalk says:

August 3, 2018 at 7:01 am

[…] if you liked this then you might like T-SQL Tuesday #104 – Code You Would Hate to Live Without […]

T-SQL Tuesday #105 - Brick Wall – MlakarTechTalk says:

August 14, 2018 at 9:01 pm

[…] If you liked this post then you might also like: T-SQL Tuesday #104 – Code You Would Hate to Live Without […]

T-SQL Tuesday #107 Invitation: Death March – MlakarTechTalk says:

October 2, 2018 at 6:00 am

[…] If you liked this post then you might also like: T-SQL Tuesday #104 – Code You Would Hate to Live Without […]

MlakarTechTalk

All Things Data | InfoSec | Privacy | Technology

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

The Scenario

The Implementation

Did you find this helpful? Please subscribe!

Like this:

5 thoughts on “T-SQL Tuesday #104 – Code You Would Hate to Live Without”

Leave a CommentCancel reply

The Scenario

The Implementation

Did you find this helpful? Please subscribe!

Share this:

Like this:

5 thoughts on “T-SQL Tuesday #104 – Code You Would Hate to Live Without”

Leave a CommentCancel reply