June 14, 2006 at 11:58 am
I tried searching for this but every time I click "Next 10" I get a blank page...
I need to get the db size (space used, extents, space free) for each db on my SQL 2000 Server. Is there a simple way to do this? Anyone have a pointer to a script that will do this?
I know that ultimately I could spend a few hours futzing with sp_msforeachdb and sp_msforeachtable to get the data, but I'm hoping someone already has something like this written.
ANy help is greatly appreciated.
June 14, 2006 at 3:13 pm
Something like that?
sp_MSForEachDB @command1='PRINT''[?]''', @command2='USE [?] EXEC sp_spaceused'
Regards,Yelena Varsha
June 15, 2006 at 2:18 am
Hi here is an extract of what I use. I still need to amend it to report available space. Hope it's of use. Any suggestion most welcome etc and I must review the thread from Yelena. Rgds Derek.
set nocount on
declare @filename varchar (100)
declare @sqlcode nvarchar (600)
declare @dbname varchar(25)
declare @dbstatus int
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @recovery_model varchar(10)
declare c1 cursor for
select name, status from master.dbo.sysdatabases (nolock)
select @bytesperpage = low
from master.dbo.spt_values (nolock)
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
open c1
fetch c1 into @dbname, @dbstatus
while @@fetch_status >= 0
begin
set @dbsize = 0
set @logsize = 0
If @dbname = 'YourDB'
Begin
select @dbsize = sum(convert(dec(15),size))
from YourDB.dbo.sysfiles (nolock)
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from YourDB.dbo.sysfiles (nolock)
where (status & 64 <> 0)
End
If @dbsize > 0
Begin
If @dbstatus <> 24
Begin
Select @Recovery_Model = 'Full'
Select 'Database' = @dbname,
'Total mdf Space' =
ltrim(str((@dbsize) / @pagesperMB,15,2) + ' MB'),
'Total ldf Space' =
ltrim(str((@logsize) / @pagesperMB,15,2) + ' MB'),
'Recovery Model' = @Recovery_Model
Select ' '
End
If @dbstatus = 24
Begin
Select @Recovery_Model = 'Simple'
Select 'Database' = @dbname,
'Total mdf Space' =
ltrim(str((@dbsize) / @pagesperMB,15,2) + ' MB'),
'Total ldf Space' =
ltrim(str((@logsize) / @pagesperMB,15,2) + ' MB'),
'Recovery Model' = @Recovery_Model
Select ' '
End
End
fetch c1 into @dbname, @dbstatus
End
deallocate c1
GO
June 15, 2006 at 4:26 am
Hi,
Try with this one It may help your purpose.
select @@servername as Server,
left(@@version, 26) as Version
declare @dbid int
declare @maxid int
declare @dbname sysname
declare @tablename varchar (255)
set @maxid = (select max(dbid) from master.dbo.sysdatabases)
set @dbid = 7 --there are 6 system objects to be excluded *change accordingly.
while @dbid <= @maxid
begin
set @dbname = (select name from master.dbo.sysdatabases where dbid = @dbid)
if (@dbname is not null)
begin --start of dbname
select top 0 name into #temptable from sysobjects where 1 = 0 --create temp table
--Get database information
exec ('set quoted_identifier off
select left(upper(''' + @dbname +'''),20) as DATABASE_NAME,
left(rtrim(filename),55) as Data_File,
rtrim(str(convert(dec(15),size)/128,15,2)) +''MB'' as size
from [' + @dbname + '].dbo.sysfiles group by size, filename, name')
exec('insert into #temptable select name from '+@dbname+'.dbo.sysobjects
where xtype = ''u'' and name <> ''dtproperties'' order by name')
--get the very first record on the temp table
set @tablename = (select top 1 name from #temptable)
while exists(select name from #temptable where name >= @tablename)
begin
exec ('select left(so.name,30) as TABLE_NAME, si.rowcnt as ROWS,
str(sum(si.dpages)) +''MB'' as DataSpaceUsed, str(sum(si.used) - sum(si.dpages)) +''MB'' as IndexSizeUsed
from '+@dbname+'.dbo.sysobjects so
inner join '+@dbname+'.dbo.sysindexes si
on so.id = si.id
where (so.xtype = ''u'') and (si.indid) = 0 and (si.indid in (0, 1, 255)) and so.name = '''+@tablename+'''
group by so.name, si.rows, si.rowcnt')
exec ('use '+@dbname+'; exec sp_helpindex '''+@tablename+'''')
set @tablename = (select top 1 name from #temptable where name > @tablename)
end
set @dbid = @dbid + 1
drop table #temptable
end --end dbname
else
set @dbid = @dbid + 1
end
June 15, 2006 at 4:37 am
Hi, I'll certainly test this against one our servers. Thanks a million and I'll update the thread later with how I got on. Thanks again, Derek
June 15, 2006 at 5:33 am
Thanks for all the suggestions. I was puttering around with it from home last night and did a sp_helpdb from Master and got all the basic info I need: Name, Size, Owner and Date Created. I don't need to know indexes or splits between data and log.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply