Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Posts
    38

    Unanswered: Create list of table names and size for a database

    Hi there,

    I am trying to create a list of all the tables in one database and then list the size of each table. So for example I want to create a table with the table name and table size for one DB

    E.g

    Table1 1111KB
    Table2 123300MB
    Table3 120448KB

    etc for all the tables in a particukar DB

    I know there is a stored procedure to list the sizes: 'sp_spaceused' but not sure how to script all this together.

    can anyone help please!!

    From

    NewToSQL

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you don't do an UPDATE STATISTICS you'll probably be dealing with GIGO, but you could use:
    Code:
    SELECT
       Coalesce(8 * Sum(CASE WHEN si.indid     IN (255)  THEN si.reserved END), 0) AS blob_kb
    ,  8 * Sum(CASE WHEN si.indid              IN (0, 1) THEN si.reserved END) AS data_kb
    ,  Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb
    ,  so.name
       FROM dbo.sysobjects AS so
       JOIN dbo.sysindexes AS si
          ON (si.id = so.id)
       WHERE  'U' = so.type
       GROUP BY so.name
       ORDER BY so.name
    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Or....

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    GO
    
    CREATE TABLE #SpaceUsed (
    			  [name] 	varchar(255)
    			, [rows] 	varchar(25)
    			, [reserved] 	varchar(25)
    			, [data] 	varchar(25)
    			, [index_size] 	varchar(25)
    			, [unused] 	varchar(25)
    )
    GO
    
    DECLARE   @tablename nvarchar(128)
    	, @maxtablename nvarchar(128)
    	, @cmd nvarchar(1000) 
     SELECT   @tablename = ''
    	, @maxtablename = MAX(name) 
       FROM   sysobjects 
      WHERE   xtype='u'
    
    WHILE @tablename < @maxtablename 
    	BEGIN
    		SELECT @tablename = MIN(name) 
    		  FROM sysobjects 
    		 WHERE xtype='u' and name > @tablename
    	 
    		   SET @cmd='exec sp_spaceused['+@tablename+']'
    		INSERT INTO #SpaceUsed EXEC sp_executesql @cmd
    	END
    
    SET NOCOUNT OFF
    GO
    
    SELECT * FROM #SpaceUsed
    GO
    
    DROP TABLE #SpaceUSed
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I generally use

    dbcc updateusage(0)
    go

    select sum(reserved)*8 as "Size in KB", object_name(id)
    from sysindexes
    where indid in (0, 1, 255)
    group by id
    order by 1 desc

    The usage statistics tend to decay over time, as Pat pointed out.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is that just the index or the index and the datapage?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Both, and text. The indid is what determines it.
    indid = 0 = heap
    indid = 1 = clustered index
    indid = 255 = text/image

    What I need is a way to subtract the nonclustered indexes, in the case that they happen to be on separate filegroups. Then I can get a script together to monitor space usage on a multi-filegroup system.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you look at my original posting, the non-clustered indicies are what are reported as index_kb. The data pages are either the heap or the clustered index, and the blob (TEXT and IMAGE) pages are just that, the index_kb are what are left.

    -PatP

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I can't be too certain, but I think that M$ drops all of the index pages into the reserved count for indids 0 and 1. For a test, I created a table, loaded a bit of data into it, ran dbcc updateusage(0), and ran both our scripts. Then create an index on the table, run the dbcc again, run both scripts, and see what changes. In the second instance, it looks like the data page count goes up for the table.
    This sort of thing just makes me a little more jealous of the Oracle DBA, who can get all of his size statistics with a lot less back-bending.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Was the index you created clustered, or non-clustered? If it was clustered, then that is exactly the behavior that I would expect. If not, then hmmmm....

    -PatP

  10. #10
    Join Date
    Jan 2004
    Posts
    38
    Thanks for your help, this worked a treat!!!!

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm just curioius, but which suggestion did you end up using?

    -PatP

  12. #12
    Join Date
    Jan 2004
    Posts
    38
    I used Brett's in the end as it was similar to what I had originally tried to do. because I then put the contents of the table to an excel spreadsheet

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Pat: I added a nonclustered index. The definitions of the columns reserved, used, and dpages in books online are almost infuriating. I don't think there is any way to derrive the number of reserved pages for a particular index on a separate filegroup from the data. I thought about just rounding the dpages value up to the next multiple of 8, but that does not take into account highly fragmented indexes. It may be that you have to run dbcc showcontig to get the actual values, but that is too resource intensive for a simple monitor.

Posting Permissions

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