Counting number of databases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Counting number of databases

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-07 : 22:29:38

Three questions please.
1. If I am running EXEC sys.sp_helpdb how do I count the number of databases returned?
2.If I am running EXEC sys.sp_helpdb how do I total/sum the database sizes?
3. How do I Select specific fields from a system stored procedure?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-07 : 22:45:57

create table #temp(DATABASE_NAME sysname,DATABASE_SIZE bigint,REMARKS char(4))

--inserting
insert into #temp
Exec sp_databases

--Counting no of databases
select count(DATABASE_NAME) from #temp

-- summing total size
select sum((DATABASE_SIZE)/1024) as DatabaseMB from #temp

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-07 : 22:57:51

I prefer to use the sys.Master_Files


select count(*) as DBCount from sys.master_Files
where type = 0  --The 0 type exclueds the log file.


select sum(cast(Size/128 as float)) from sys.master_Files
where type = 0  --The 0 type exclueds the log file.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-07 : 22:59:52

What if you have multiple files Vinnie?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-07 : 23:43:17

Why don't just count rows in sysdatabases?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-08 : 00:35:34

quote:
Originally posted by sodeep

What if you have multiple files Vinnie?

You're correct on if you have multiple files you would need to add a group by. I am aware this method may not be the best for everyone, I have never seen any BOL supporting using it for getting database size infomation. I just like it because it is the best table/Information I have come accross to referance sizs w/o being forced to use a built-in sp.

There may be a better way out there, I really havn't spent all that much time looking for an alternative, this seems to work very well for me.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-08 : 10:11:53

The reason I said is that it is not going to give you accurate sum of all database sizes. Try it and see.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-08 : 12:15:12

Sodeep,

You are again correct. Here is the CORRECTED query using the method I described. You can query whatever information you want to retrieve about the db's from modifying this. I added a join to show how to use in a real scenerio.


select b.[Name],a.database_ID,a.DBSizeMB,*
from 
(
Select aa.database_ID,SUm(cast(aa.[Size] as float)/128)  as [DBSizeMB]
from
sys.master_Files aa
group by aa.DataBase_ID
) a
inner Join
Sys.Databases b
on a.Database_ID = b.DataBase_ID

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-08 : 14:30:15

Thats nice. You can get additional informations about databases than mine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources