Script para listar todos os bancos de dados da instância utilizando sp_msforeachdb.

IF object_id('Recovery') is not null

DROP TABLE Recovery

CREATE TABLE Recovery(

[name] sysname NOT NULL,

[RecoveryModel] nvarchar(128) NOT NULL

)

--sp_msforeachdb: http://blogs.techrepublic.com.com/datacenter/?p=292

INSERT INTO Recovery

EXECUTE master.dbo.sp_msforeachdb 'select DB_NAME(DB_ID(''?'')) as name, convert(sysname,DatabasePropertyEx(DB_NAME(DB_ID(''?'')),''Recovery'')) as RecoveryModel'

SELECT * FROM Recovery

IF object_id('Recovery') is not null

DROP TABLE Recovery