Looking for SQL Server code to do what Oracle was able to do

[DeepDiverMom]

I must generate a management report (today) from data gathered from each database server in the company. We have a preponderance of Oracle servers (50+) against which I have successfully run the following code. I must also gather similar statistics for our DB2, Sybase, SQL Server, and MySQL instances. I am having difficulty finding appropriate "across-the-db-server" data dictionary views for these remaining four vendors' db server environments that I was able to access for Oracle. I believe it is my relative lack of familiarity with these other vendors' db engines that is standing in my way. Could anyone please propose SQL code for the SQL Server SQL environment that can achieve what I did in Oracle?

My specific need is for a single line of output (per db server instance/invocation) that displays
a) Host (machine) name upon which this db server runs,
b) DB Instance name (if applicable/available),
c) DB version for this instance,
d) Number of DB logins/users for this db instance,
e) Total number of tables+indexes across the entire db instance (i.e., across all schemas), and finally
f) Total amount of space that all tables and indexes consume collectively on this database instance.

Note: I have used '^' to separate values to facilitate reading results into an Excel spreadsheet as delimited values.)

Here is the code that I used successfully against each of our Oracle instances.

select	host_name||'^'||instance_name||'^'||version||'^'||
	users||'^'||cnt||'^'||storage_space
from	 v$instance
	,(select count(*) cnt,sum(bytes) storage_space from dba_segments)
	,(select count(*) users from dba_users)
/

durango^durprod^9.2.0.4^107^23894^5878298816

My colleagues here tell me that the non-Oracle databases do not have the same across-the-db-instance-query capabilities that Oracle has. I would hate to find out that they are right. Can anyone please help me prove them all wrong with a working piece of SQL Server SQL code? If the "nay-sayers" are correct, please advise me of that sad fact, as well.

There is certainly appropriate gratitude at the end of this for anyone who can help me resolve this issue.

Thanks,

Becca

 

[JayKusch]

Your Oracle buds are not exactly correct ... although there is no pretty stored procedure that will give this info, you can create a script to pull just about all of the info you desire.

The tricky part is the "across-the-db-instance-query capabilities". But that can be accomplished with an undocumented SP called ... sp_MSForEachDB.

Here is an example you can run in Query Analyzer on your SQL Server box:

EXEC sp_MSForEachDB 'USE ? SELECT DB_Name() EXEC sp_help'


Thanks

J. Kusch

 

[DeepDiverMom]

J.

Thanks for this suggestion. When I went to try it, I received the following error:

1> EXEC sp_MSForEachDB 'USE ? SELECT DB_Name() EXEC sp_help'
2> go
Msg 2812, Level 16, State 62, Server DURANGO, Procedure , Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure.

Is this undocumented SP version dependent? This server is running SQL Server 6.0. (But was also have several 7.0s and Server2000s, as well).

Looking forward to your advice,

Becca