$sanjayattray (3/18/2008)
Use masterdeclare @DBName varchar(35),
@STR varchar (255)
declare DBRecoveryModelGenerator_cursor cursor for
select name from sysdatabases
where category in ('0', '1','16')
order by name
open DBRecoveryModelGenerator_cursor
fetch next from DBRecoveryModelGenerator_cursor into @DBName while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
select @STR = 'SELECT DATABASEPROPERTYEX ('''+ @DBName + ''', ''Recovery'')' + @DBName
exec (@str)
end fetch next from DBRecoveryModelGenerator_cursor into @DBName end
close DBRecoveryModelGenerator_cursor
DEALLOCATE DBRecoveryModelGenerator_cursor
go
Hi Sanjay,
Thanks for replying. It seems to me that you are looping through the list of databases and printing the recovery model of each database.
I would normally stay away from cursor and just use the following query. I don't mean to be bashing your method, just a suggestion.
[font="Courier New"]SELECT [name] AS [DatabaseName], CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery')) AS [RecoveryModel] FROM master.dbo.sysdatabases ORDER BY name[/font]
WRACK
CodeLake