query to get dbname,totalsize,reservedsize of all databases in sql 2000

• syamala.popuri

  Mr or Mrs. 500

  Points: 597

  More actions

  October 12, 2010 at 4:15 am

  #227917

  Hi,

  required query for me to work on sql 2000 server is

  query to get dbname,totalsize,reservedsize of all databases in sql 2000.

  Any body have so or query ples let me know.

  Thanks in advance

  Syamala

• tommyh

  SSCertifiable

  Points: 6302

  More actions

  October 12, 2010 at 6:00 am

  #1235430

  No perfect answer but maybe it can push you in the right direction.

  create table #tbl (name varchar(128), s bigint)

  declare @db varchar(128)

  declare @sql varchar(2048)

  select @db = ''

  while exists (select Name from master..sysdatabases where name > @db)

  begin

  select top 1 @db = Name from master..sysdatabases where name > @db order by Name

  select @sql = 'insert into #tbl (Name, s) select ''' + @db + ''', sum(size) from ' + @db + '..Sysfiles'

  exec(@sql)

  end

  select * from #tbl

  drop table #tbl

  /T