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 tables in 2 databases using JDBC.

I need to Query tables in 2 databases after getting a connection. I cannot make this piece of code to work. In db2, oracle this type of code works.

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String sys = jdbc:microsoft:sqlserver://192.168.0.152";
conn = DriverManager.getConnection(sys, "name", "pswd");
Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM pubs.jobs");

rs = stmt.executeQuery("SELECT * FROM mydb.myfile");

If I run this I get invalid object pubs.jobs error.

Any ideas?
Thanks.You are missing the dbo (object owner) in the table names. You want to use something more likeClass.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String sys = jdbc:microsoft:sqlserver://192.168.0.152";
conn = DriverManager.getConnection(sys, "name", "pswd");
Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM pubs.dbo.jobs");

rs = stmt.executeQuery("SELECT * FROM mydb.dbo.myfile"); I think that should get you rocking and rolling nicely.

-PatP|||Thanks Patp!
Now it works.
is dbo something that has to be included? I mean is it always the owner of tables?
Sorry I am a sql server newbie and know very little about it.|||dbo is "data base owner", and they normally "own" all of the objects in a production database. You can think of different owners in SQL Server much like different schemas in Oracle.

When using "one part names", such as a table name or a procedure name by itself, you don't need to use dbo. When using anything more than one part names (including the database), you should always use the owner name even though it can be allowed to default so that master.dbo.sysdatabases and master..sysdatabases are usually the same thing. Explicitly providing dbo allows object references to compile without the need for later lookup, so it also helps performance to always explicitly provide the dbo.

-PatP|||Thanks alot Pat!