Hello Guys,

I was looking for some simple and fast way to generate a list for dettach and attach databases, but the most of scripts on the internet uses cursor and as i really hate cursors, i decided to write my own scripts, because it is a good way to learn and improve your T-SQL Skills.

So, i will put the code for two scripts below and you could download these scripts here

Dettach Databases

— If you put 1 the databases will be dettached
SET nocount ON
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DatabaseName NVARCHAR(512)
DECLARE @ExecuteNow BIT
SET @ExecuteNow = 0
DECLARE @table TABLE
(
id INT ,
dbName NVARCHAR(512) ,
isVerified BIT
)
INSERT  INTO @table
SELECT  database_id ,
name ,
0 AS isVerified
FROM    sys.databases
WHERE   name NOT IN ( ‘master’, ‘tempdb’, ‘model’, ‘msdb’,
‘ReportServerTempDB’,
‘ReportServer’, ‘distribution’ )
ORDER BY database_id

WHILE ( SELECT  COUNT(*)
FROM    @table
WHERE   isVerified = 0
) > 0
BEGIN
SELECT TOP ( 1 )
@DatabaseName = dbName
FROM    @table
WHERE   isVerified = 0

IF @ExecuteNow = 1
BEGIN
SET @SQL = ‘exec sp_detach_db ”’ + @databaseName + ””
EXEC sp_executesql @sql
END

PRINT ‘exec sp_detach_db ”[‘ + @databaseName + ‘]”’

UPDATE  @table
SET     isVerified = 1
WHERE   @DatabaseName = dbName
END
SET nocount OFF

And now how to attach databases

SET nocount ON
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DatabaseName NVARCHAR(512)

DECLARE @table TABLE
(
id INT ,
dbName NVARCHAR(512) ,
isVerified BIT
)
INSERT  INTO @table
SELECT  database_id ,
name ,
0 AS isVerified
FROM    sys.databases
WHERE   name NOT IN ( ‘master’, ‘tempdb’, ‘model’, ‘msdb’,
‘ReportServerTempDB’,
‘ReportServer’, ‘distribution’ )
ORDER BY database_id

WHILE ( SELECT  COUNT(*)
FROM    @table
WHERE   isVerified = 0
) > 0
BEGIN
SELECT TOP ( 1 )
@DatabaseName = dbName
FROM    @table
WHERE   isVerified = 0

SET @SQL = ( SELECT TOP ( 1 )
‘exec sp_attach_db ”’ + @DatabaseName + ”’ , ‘
+ ( SELECT  ”” + physical_name + ””
FROM    sys.master_files
WHERE   database_id = DB_ID(@DatabaseName)
AND RIGHT(physical_name, 3) = ‘mdf’
) + ‘ , ‘
+ ( SELECT  ”” + physical_name + ””
FROM    sys.master_files
WHERE   database_id = DB_ID(@DatabaseName)
AND RIGHT(physical_name, 3) = ‘ldf’
)
FROM   @table
)

PRINT @sql

UPDATE  @table
SET     isVerified = 1
WHERE   @DatabaseName = dbName

END
SET nocount OF

So, that’s a simple and good way to generate attach and dettach, it isn’t the best, but can help you.

Regards,
Marcos Freccia