Search

Thursday, July 28, 2011

Cursor to create sp_detach db script for all user databases

This cursor will crete sp_detach_db code for every database on an instance execpt for system databases.

use master
go
declare @cdatabasename nvarchar(128)
declare @string nvarchar(128)
declare Nonsysdatabases cursor for
select [name] from sysdatabases where dbid > 4 order by [name]
open Nonsysdatabases
set @string = '' + @@servername +''
fetch next from Nonsysdatabases
into @cdatabasename
while @@fetch_status = 0
begin
set @string = 'exec sp_detach_db ' +'''' + @cdatabasename + ''''+ ', ' + '''true'''
print @string
set @string = 'go'
print @string
fetch next from Nonsysdatabases
into @cdatabasename
end
close Nonsysdatabases
deallocate Nonsysdatabases

No comments:

Post a Comment