July 2, 2008 at 11:50 am
How would I write a sql statement that allowed me to go through all my databases and get the size of each one. Store the size in a variable and increment the size by each database I process. I would also need to dynamically change the database to get the sizes from? Each time I change databases all variable get reset.
Thanks!
July 2, 2008 at 12:34 pm
This is Just a Floop Code, might need some corrections.. But check to see if this is what you need.
Declare @size1 int
Declare @size2 int
set @size2 = 100
Declare @name1 sysname
Declare db_cur cursor FOR
select (size*8)/1024 as size,name from sysaltfiles where fileid=1 and dbid >4 Order by name
Open db_cur
fetch next from db_cur into @size1,@name1
while @@fetch_status=0
BEGIN
SELECT @size1+@size2,@name1
fetch next from db_cur into @size1,@name1
END
Close db_cur
Deallocate db_cur
Maninder
www.dbanation.com
July 2, 2008 at 12:54 pm
You could select from the sys.sysaltfiles DMV, and use a running total expression for the size. That would give you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply