query to list db and recovery model

I want a query that would list all databases on a server along with its
current recovery model setting such as
DB1 Simple
DB2 FullHassan wrote:
> I want a query that would list all databases on a server along with
> its current recovery model setting such as
> DB1 Simple
> DB2 Full
Exec sp_MSForEachDB 'SELECT ''?'' as "Database",
DATABASEPROPERTYEX(''?'', ''Recovery'') as "Recovery"'
or
Create Table #DBRecovery (
db_name nvarchar(128),
recovery nvarchar(30) )
Insert Into #DBRecovery
Exec sp_MSForEachDB 'SELECT ''?'', CAST(DATABASEPROPERTYEX(''?'',
''Recovery'') as nvarchar(30))'
select * from #DBRecovery
drop table #DBRecovery
David Gugick
Imceda Software
www.imceda.com|||Hi Hassan,
If you want it just as an information, you can use sp_helpdb to get the list
of all the database and their recovery properties, assuming you have access
to all the databases in the server.
--
Thanks
Yogish|||No need to get complicated...
select [name],databasepropertyex([name],'Recovery') as [Recovery]
from master.dbo.sysdatabases
order by [name]
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eb399oELFHA.1948@.TK2MSFTNGP14.phx.gbl...
>I want a query that would list all databases on a server along with its
> current recovery model setting such as
> DB1 Simple
> DB2 Full
>|||"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<e8VIdEOLFHA.568@.TK2MSFTNGP09.phx.gbl>...
> No need to get complicated...
> select [name],databasepropertyex([name],'Recovery') as [Recovery]
> from master.dbo.sysdatabases
> order by [name]
> --
> HTH
>
Jasper
Excellent simple answer. My question is: why does this work? I didn't
realise that databasepropertyex could use the fieldname in that way.
Is this a general featrure of T-SQL to recognize a field reference
before interpreting as a string?
Steve|||All functions work this way. The function takes a string as parameter. That string can be a
constant, as in 'pubs', or a column name which derives the value for each row for the specified
column used in a SELECT statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"steve" <stevester@.freeuk.com> wrote in message
news:73627c14.0504150739.64492ae8@.posting.google.com...
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:<e8VIdEOLFHA.568@.TK2MSFTNGP09.phx.gbl>...
>> No need to get complicated...
>> select [name],databasepropertyex([name],'Recovery') as [Recovery]
>> from master.dbo.sysdatabases
>> order by [name]
>> --
>> HTH
> Jasper
> Excellent simple answer. My question is: why does this work? I didn't
> realise that databasepropertyex could use the fieldname in that way.
> Is this a general featrure of T-SQL to recognize a field reference
> before interpreting as a string?
> Steve