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

Query to find out actual database size.?

Hi,
I would like to know that actual database size.
For eg.. I have a database DB1 on executing sp_databases,
the size I get is 50GB. But 40GB is actually used and rest
is free.
What query should I give to get actual database size.'
Thanks,
Sid.If you do an sp_spaceused you can see the unuesed and used space, so just
subtract the 2
Dylan
"Sid" <sid00@.rediffmail.com> wrote in message
news:016001c38ef7$a3cea100$a001280a@.phx.gbl...
> Hi,
> I would like to know that actual database size.
> For eg.. I have a database DB1 on executing sp_databases,
> the size I get is 50GB. But 40GB is actually used and rest
> is free.
> What query should I give to get actual database size.'
> Thanks,
> Sid.|||Thanks for the help.
Sid.
>--Original Message--
>If you do an sp_spaceused you can see the unuesed and
used space, so just
>subtract the 2
>Dylan
>"Sid" <sid00@.rediffmail.com> wrote in message
>news:016001c38ef7$a3cea100$a001280a@.phx.gbl...
>> Hi,
>> I would like to know that actual database size.
>> For eg.. I have a database DB1 on executing
sp_databases,
>> the size I get is 50GB. But 40GB is actually used and
rest
>> is free.
>> What query should I give to get actual database size.'
>> Thanks,
>> Sid.
>
>.
>