Cursor Disconnecting Database Before Detach Operation in SQL Server

Hello everyone,

In this article, I will talk about the use of cursor that disconnects the database before detach in SQL Server.

Actually I wrote this Cursor on a question. Thinking it might be useful to someone.

While detaching in SQL Server, you may encounter errors upon connection. These errors can be solved with short written codes, or you can solve them using the Cursor I have written below.

DECLARE @dbname AS VARCHAR(80);
DECLARE @server_name AS VARCHAR(20);
SELECT @server_name = @@servername;
DECLARE rs_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ( N'TestIslem' ); --In this section, you can select as many databases as you want. 
OPEN rs_cursor;
FETCH NEXT FROM rs_cursor
INTO @dbname;
IF @@FETCH_STATUS <> 0
    PRINT 'There is no database to terminate the connection... Please check your script!!!';
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE');
    EXEC ('ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS');
    EXEC ('ALTER DATABASE ' + @dbname + ' SET MULTI_USER');
    FETCH NEXT FROM rs_cursor
    INTO @dbname;
    PRINT 'Print ''SERVER NAME : ' + UPPER(@server_name) + '--> Successfully disconnected for all databases.''';
END;
CLOSE rs_cursor;
DEALLOCATE rs_cursor;

When you run the code, where I used a database called TestIslem. This will terminate any open connections on the database. In this way, you will successfully perform the detach process.

Good luck to everyone in business and life.