Attach & Detach all databases

While working in my current project, I faced many scenarios where I have to attach one or more Databases and then detaches these databases. Later I found one script here which can easily generate the “attach” code for each database, make any path or filename changes necessary, and re-attach all the databases with a single mouse click.

Refer the below script and test this before you run in Production environment:

USE [master];

DECLARE @database NVARCHAR(200),
@cmd NVARCHAR(1000),
@attach_cmd NVARCHAR(4000),
@file NVARCHAR(1000),
@i INT;

DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY FOR
SELECT  RTRIM(LTRIM([name]))
FROM    sysdatabases
WHERE   [dbid] > 4  -- exclude system databases

OPEN dbname_cur
FETCH NEXT FROM dbname_cur INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @i = 1;

-- Initial attach command stub
SET @attach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
+ 'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10);

-- Get a list of files for this database
DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY FOR
SELECT  physical_name
FROM    sys.master_files
WHERE   database_id = db_id(@database)
ORDER   BY [file_id];

OPEN dbfiles_cur
FETCH NEXT FROM dbfiles_cur INTO @file
WHILE @@FETCH_STATUS = 0
BEGIN
SET @attach_cmd = @attach_cmd + '    ,@filename' + CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + '''' + CHAR(10);
SET @i = @i + 1;
FETCH NEXT FROM dbfiles_cur INTO @file
END

CLOSE dbfiles_cur;
DEALLOCATE dbfiles_cur;

-- Output "attach" command.
PRINT @attach_cmd;

-- DETACH (uncomment the following line at your peril - IT WILL DETACH ALL USER DATABASES!)
/*EXEC sp_detach_db @dbname = @database, @skipchecks = 'true';  -- change this to false if you want it to update stats before detaching*/

FETCH NEXT FROM dbname_cur INTO @database
END

CLOSE dbname_cur;
DEALLOCATE dbname_cur;