Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    31

    Unanswered: How to show all tables info in Task Pad?

    In the table view in the Task Pad view, it lists the number of rows and size of each table, which is great, however it only lists the first 25 tables or so and there is no scroll function.

    1. Does anyone know how I can see this info in Task Pad for all tables, without having to use the search function and look up 200+ tables one-by-one?

    2. Does anyone know of another utility or statement to run against the DB which will return this info all at once for all the tables?

    Thanks.

  2. #2
    Join Date
    Apr 2004
    Posts
    101
    Task pad should show Next and Last options in the bottom of the page.
    You can also get all user table info by executing this sql..
    select * from information_schema.tables where table_type like 'BASE TABLE'

  3. #3
    Join Date
    Mar 2004
    Posts
    31

    Don't see it

    First, in TaskPad, there is no next or last button, second that line of code you gave:

    select * from information_schema.tables where table_type like 'BASE TABLE'

    Did not return the # of rows and KB size of all of my tables.

    THis is what I am looking for.

    Anyone else know?

    I ran the "SP_help" and "SP_tables" stored procedures, but they don't return the table row count or size.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest:
    Code:
    SELECT CAST(Coalesce(Sum(si.reserved) / 128.0, 0) AS DECIMAL(5, 2)) AS total_mb
    ,  CAST(Coalesce(Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) 
    /     128.0, 0) AS DECIMAL(5, 2)) AS data_mb
    ,  CAST(Coalesce(Sum(CASE WHEN si.indid = 255     THEN si.reserved END) 
    /     128.0, 0) AS DECIMAL(5, 2)) AS blob_mb
    ,  CAST(Coalesce(Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN
          si.reserved END) / 128.0, 0) AS DECIMAL(5, 2)) AS index_mb
    ,  Object_Name(si.id)
       FROM dbo.sysindexes AS si
       GROUP BY si.id
    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    31

    ??

    Pat,

    What does that code do. Here was my output:

    (20 row(s) affected)

    Server: Msg 8115, Level 16, State 8, Line 1
    Arithmetic overflow error converting numeric to data type numeric.
    Warning: Null value is eliminated by an aggregate or other SET operation.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sp_spaceused?

    EDIT: Found this...

    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.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rweinstein
    Pat,

    What does that code do. Here was my output:

    (20 row(s) affected)

    Server: Msg 8115, Level 16, State 8, Line 1
    Arithmetic overflow error converting numeric to data type numeric.
    Warning: Null value is eliminated by an aggregate or other SET operation.
    Change the 5s to 15s and try again.

    It shows some interesting space observations, by table.

    -PatP

  8. #8
    Join Date
    Mar 2004
    Posts
    31

    Pat...

    pat,

    That returned data, but the data_mb figures seem to be close to half of the actual size. For example, the size of a table from TaskPad is 79656 KB and your query generates 38.94 MB.

    Is this what is expected? Is the data_mb column the table size?

    Thanks for the help, it is greatly appreciated.

  9. #9
    Join Date
    Mar 2004
    Posts
    31

    Thanks, Brett

    Brett,

    Wonderful!!!!!!!!!!

    That was it!!!!!!

    Thanks a million!!!!!!

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does my total match the taskpad total?

    -PatP

Posting Permissions

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