InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 10:13:34 GMT20http://www.sqlservercentral.com/Forums/Topic674074-146-1.aspx[quote][b]JMSM (3/12/2009)[/b][hr][u][b]Q2: [/b][/u]select @@servername as 'instance name', count(*) as 'Nº of DBs by Instance', sum(a.size) as 'espaço alocado (mb)', sum(a.used) as 'espaço utilizado (mb)', sum(a.size)-sum(a.used) as 'espaço livre (mb)'from db_name..tbl_name a, sys.sysdatabases b[/quote]You've got a cross join (Cartesian product) there. Each row of db_name..tbl_name is going to match to each and every row on sysdatabases.Thu, 12 Mar 2009 14:02:45 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic674074-146-1.aspxThanks a lot Jamie (everybody). Your answer was very useful to my work.Regards,JMSM ;)Thu, 12 Mar 2009 13:46:05 GMTJMSMhttp://www.sqlservercentral.com/Forums/Topic674074-146-1.aspx[quote]select @@servername as 'instance name', count(*) as 'Nº of DBs by Instance', sum(a.size) as 'espaço alocado (mb)', sum(a.used) as 'espaço utilizado (mb)', sum(a.size)-sum(a.used) as 'espaço livre (mb)'from db_name..tbl_name a, sys.sysdatabases b[/quote]Your count is high because your are joining with whatever db_name..tbl_name is producing 7921 records. quick fix, run two queries:[quote]select @@servername as 'instance name', (select count(name) from sys.sysdatabases) as 'Nº of DBs by Instance', sum(a.size) as 'espaço alocado (mb)', sum(a.used) as 'espaço utilizado (mb)', sum(a.size)-sum(a.used) as 'espaço livre (mb)'from db_name..tbl_name a, sys.sysdatabases b[/quote]Thu, 12 Mar 2009 05:48:58 GMTJamieXhttp://www.sqlservercentral.com/Forums/Topic674074-146-1.aspxuse sp_helpdbThu, 12 Mar 2009 05:35:59 GMTjk.returnshttp://www.sqlservercentral.com/Forums/Topic674074-146-1.aspxI need to get the number of databases on one instance with Total Sapce Used, Total Space Alocated and Total Free SpaceThe point is that in the second query result [u][b]Q2: [/b][/u] the result of the number of databases appear completely out of the reality :DCan anyone explain me what am i doing wrong.Sorry :sick:Thanks and regards,JMSM ;)[u]Q1:[/u] select count(name) from sys.sysdatabasesresult:89[u][b]Q2: [/b][/u]select @@servername as 'instance name', count(*) as 'Nº of DBs by Instance', sum(a.size) as 'espaço alocado (mb)', sum(a.used) as 'espaço utilizado (mb)', sum(a.size)-sum(a.used) as 'espaço livre (mb)'from db_name..tbl_name a, sys.sysdatabases bresult: INamXYZ [b][font="Arial Black"]7921[/font][/b] 40405288 23075475 17329813[u]Q3:[/u] select @@servername as 'instance name', count(*) as 'Nº of DBs by Instance'from sys.sysdatabasesresult:INamXYZ 89Thu, 12 Mar 2009 05:01:11 GMTJMSM