Help With Looping! - 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

General SQL Server Forums

Script Library

Help With Looping!

Author

Topic

GM1
Starting Member

2 Posts

Posted - 2004-09-29 : 06:36:07

Hi there, I am new to SQL and am having trouble looping a script. I have the following script that needs to be refreshed a large number of times, or needs to be looped indefinitely until stopped:

select
df.tablespace_name "Tablespace",
block_size "Block Size",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
dba_tablespaces ts,
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from dba_free_space
group by tablespace_name) fs
where
ts.tablespace_name = fs.tablespace_name
and
df.tablespace_name = fs.tablespace_name(+)
;

I know this question may have a very easy solution, but I have no idea how to solve it.

Thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-29 : 08:18:52

an infinite loop can be done with
while 1=1
begin
..... do stuff....
end

but why do you want an infinite loop?

Go with the flow & have fun! Else fight the flow

GM1
Starting Member

2 Posts

Posted - 2004-09-29 : 10:50:57

This script is showing me the status of our tablespaces. I need to watch them all the time, but this script is just providing me with a snapshot, thus not updating. Therefore I have to keep issuing the same command to run the script every couple of minutes. Thinking about it, I may not need to have the script loop, just refreshing would do the trick as well!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-29 : 10:55:19

that's true.

take a look at WAITFOR in BOL. that may give you some ideas.

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-01 : 03:31:44

can you just schedule the script to run every minute?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources