Looking for Sybase 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 Sybase 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 Sybase 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

 

[scriptdan]

Sybase stores data a little differently than Oracle. Also, even though users have a default db (schema) that they are associated with, they can work in different db's if they are given permission.

Here are some snippets. Not really sure what you are hoping to gain from items e and f. You can run sp_spaceused for each db on the server (instance)

1> exec testdb..sp_spaceused
go
database_name database_size
------------------------------ -------------
testdb 7250.0 MB
reserved data index_size unused
--------------- --------------- --------------- ---------------
796276 KB 359742 KB 408952 KB 27582 KB
(return status = 0)

Items a-d of what you asked for.
==========================================
select host_name() as host,
@@servername as instance_name,
db_name(sp.dbid) as schema_name,
@@version,
suser_name(sp.suid) as login,
count(*) as db_login_count
from master..sysprocesses sp
where suser_name(sp.suid) like '%qa'
group by db_name(sp.dbid), suser_name(sp.suid)