September 5, 2008 at 11:56 pm
Comments posted to this topic are about the item Monitor file growth - a better way for sp_spaceused
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
October 13, 2008 at 4:23 am
Looks good but how do you go about getting the same information for all databases on a SQL instance.
i.e I'm trying to check when a database is about to auto-extend and I just want the available space for DB1, DB2 etc.
The USE command doesnt seem to permit the construct
USE @dbName. You have to implicitly specify the database i.e USE "DB1"
KR
Ian
October 22, 2008 at 7:25 am
Sorry about the late reply, was swamped at work. 🙂
One way to do it for each database is either the undocumented sp_msforeachdb feature or roll it into a loop. You can use cursors but I personally don't use them much. Here is a non-cursor version:
declare @db_list table (dbname sysname)
declare @dbname sysname, @query varchar(8000)
insert @db_list(dbname)
select name from sys.databases
where name not in ('tempdb', 'master', 'msdb', 'model') -- feel free to change as needed
order by name asc
while (select count(*) from @db_list) > 0 -- one way to iterate, the list is small enough that this will have no impact
begin
select top 1 @dbname = dbname from @db_list
select @query = 'use ' + quotename(@dbname) + '; INSERT THE NEW SP_SPACEUSED SCRIPT HERE'
exec(@query)
delete from @db_list where dbname = @dbname
end
Hope this helps and good luck.
Gaby A.
Toronto, ON
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply