USE [master]  GO  EXEC master.dbo.sp_detach_db @dbname = N’AdventureWorks2012′  GO
–Script for detach all dbs.
select distinct ‘USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N”’+db_name(database_id) +”’
GO’ from sys.master_files where database_id > 5 order by 1
/*
Be careful for report db
*/

———————————————————————–

/*
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N’D1′
GO
*/

create table #t111(
[database_id] int, physical_name_count int
)
insert into #t111([database_id],physical_name_count)
select d.[database_id], count(mf.physical_name) from sys.databases d, sys.master_files mf where d.name not in (
‘master’,
‘tempdb’,
‘model’,
‘msdb’
) and d.database_id = mf.database_id group by d.[database_id]
order by 1

select * from #t111
declare @dbi int , @pcnt int , @pname nvarchar(2000)
declare cur cursor for
select t.database_id, t.physical_name_count, mf.physical_name from sys.master_files mf ,#t111 t
where t.database_id = mf.database_id
open cur
fetch next from cur into @dbi , @pcnt , @pname
while @@FETCH_STATUS = 0
begin

select @dbi , @pcnt , @pname
fetch next from cur into @dbi , @pcnt , @pname
end
close cur
deallocate cur

select * from sys.master_files
–Script for detach all dbs.
select distinct ‘USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N”’+db_name(database_id) +”’
GO’ from sys.master_files where database_id > 5 order by 1
/*
Be careful for report db
*/

USE [master]  GO  EXEC master.dbo.sp_detach_db @dbname = N’AdventureWorks2012′  GO