What do you do when you have to detach every single user
database on a given SQL Instance? Isn’t it tedious to type the detach command
for every single db? If you hate doing that, you came to the right place! Use
the below script to generate the detach commands which can be executed to
detach the dbs. When there are active spids in a database, the detach command
is going to fail. One way around that is to kill all the connections in a
database. I have got an even better option. You can set the database to offline
mode which kills all the active connections forcefully, set it back to online
immediately and then detach the db rightaway. That will kill the connections and detach the dbs in one shot.
set quoted_identifier off
declare @dbname varchar(100)
declare @string varchar(3000)
declare cursor1 cursor
for
select name from sysdatabases where dbid>4
open cursor1
fetch next from cursor1 into @dbname
while (@@fetch_status
= 0)
begin
select @string="use master
go
alter database "+@dbname +" set offline with
rollback immediate
go
alter database "+@dbname +" set online
go
sp_detach_db "+@dbname +"
go"
print @string
fetch next from cursor1 into @dbname
end
close cursor1
deallocate cursor1
