-1

I want to join server name ,database name and database size in a single select query.

Server Name Database Name   Database Size in MB Report_Time  
SYBD012     CBA_ATM         3856.81 MB          08-02-17 16:40   
SYBD012     CBA_CM_PPP      398203.94 MB        08-02-17 16:40  
SYBD012     CBA_CM_TTT      38436.81 MB         08-02-17 16:40  
SYBD012     CM_C01          32764.00 MB         08-02-17 16:40  
SYBD012     ECMStaging      1985.25 MB          08-02-17 16:40  
SYBD012     FNMPDataWarehou 601.50 MB           08-02-17 16:40

I tried this query but still I didn't able to take servername from sysservers.

select name,sum(size)/512 MB from sysusages u ,sysdatabases d where d.dbid=u.dbid
halfer
  • 19,824
  • 17
  • 99
  • 186
Rajendran S
  • 1
  • 1

2 Answers2

1

You cannot join with sysservers. It has no meaning. sysservers holds the servers on which this current server can connect and execute remote procedures.

If you'd like to get your current server name, You can get it with: select @@servername

In any case, the correct way to get all your databases size is (assuming your page size is 2):

select
    sum(size) * 2 / 1024 db_sizeMB,
    db_name(dbid) database_name
from
    master.dbo.sysusages u
group by
    dbid
Ofir Winegarten
  • 9,215
  • 2
  • 21
  • 27
0

If I have understood your question properly you will have to use three part name in the query. Something like this select from model.dbo.sysusages mds where mds. = ''

BoringProgrammer
  • 91
  • 1
  • 11