Results 1 to 4 of 4

Thread: Database size

  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Database size

    Is there any way I can join all the information produced by this into a single output rather than multiple outputs

    EXEC sp_MSforeachtable @command1=" EXEC sp_spaceused '?'"


    Like this
    Listing_Images 43 16 KB 8 KB 8 KB 0 KB
    User 3 16 KB 8 KB 8 KB 0 KB

    I want to fill a gridview in ASP but don't know how to deal with data produced through multiple queries

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Look at the code of sp_spaceused and build what you need
    Code:
    select name=object_name(i.id)
    ,rows=sum(case when indid<2 then rows else 0 end)
    ,reservKB=convert(int,sum(reserved)/.125)
    ,dataKB=convert(int,sum(case
      when indid<2 then dpages
      when indid=255 then isnull(used,0)
      else 0 end)/.125)
    ,indexKB=convert(int,(sum(used)-sum(case
      when indid<2 then dpages
      when indid=255 then isnull(used,0)
      else 0 end))/.125)
    ,unusedKB=convert(int,(sum(reserved)-sum(used))/.125)
    from sysindexes i join sysobjects o on i.id=o.id
    where indid in (0, 1, 255)
      and o.xtype='U'
    --  and id in (object_id('Listing_Images'), object_id('User'))
    group by i.id
    else
    Code:
    create table #t1 (Table_name varchar(128), Records varchar(11)
    ,reservedKB varchar(18), dataKB varchar(18), index_sizeKB varchar(18)
    ,unused varchar(18)) 
    insert #t1 exec sp_spaceused Listing_Images
    insert #t1 exec sp_spaceused User 
    select * from #t1
    drop table #t1
    Last edited by pdreyer; 11-06-07 at 03:44.

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    I see what you mean, Ive done the same thing at an application level where it executes and inserts each result as a row,

    PHP Code:
           While myReader.Read
                        Dim newRow 
    As DataRow dt.NewRow()
                        
    newRow("name") = myReader("name").ToString
                        newRow
    ("rows") = myReader("rows").ToString
                        newRow
    ("data") = myReader("data").ToString
                        newRow
    ("index_size") = myReader("index_size").ToString
                        newRow
    ("reserved") = myReader("reserved").ToString
                        newRow
    ("unused") = myReader("unused").ToString
                        dt
    .Rows.Add(newRow)

                        
    Total_IndexSize Total_IndexSize Convert.ToInt32(myReader("index_size").ToString().Split(SpaceDelimiter)(0))
                        
    Total_DataSize Total_DataSize Convert.ToInt32(myReader("data").ToString().Split(SpaceDelimiter)(0))
                        
    Total_ReservedSpace Total_ReservedSpace Convert.ToInt32(myReader("reserved").ToString().Split(SpaceDelimiter)(0))
                        
    Total_UnusedSpace Total_UnusedSpace Convert.ToInt32(myReader("unused").ToString().Split(SpaceDelimiter)(0))
                    
    End While 

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Updated my previous post

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •