table size

  • is there a command like sp_helpdb that will show the size of all the tables in a particular database?

  • EXEC sp_MSforeachtable 'sp_spaceused ''?'''

  • I don't know if there is a builtin command for what you want.

    I use this one:

    CREATE PROCEDURE sp_rpttblspc (@dbname varchar(30) = null, @tblname varchar(31) = null)

    as

    /********************************************************************************/

    /*Created BY :Umachandar Jayachandran(UC)*/

    /*Created ON :15 April 1996*/

    /*Description:Used TO CREATE a report OF tables & number of rows.*/

    /********************************************************************************/

    /*Resources :http://www.umachandar.com/resources.htm */

    /********************************************************************************/

    SET nocount ON

    DECLARE @tablename varchar(30), @cmdstr varchar(255)

    SELECT @dbname = isnull(@dbname, db_name()), @tblname = isnull(@tblname , '') + '%'

    IF db_id(@dbname) IS NULL

    BEGIN

    raiserror('Invalid DATABASE name was specified.', -1, -1)

    return(1)

    END

    CREATE TABLE #tblspc

    (name varchar(30), rows varchar(10), reserved varchar(20), data varchar(20),

    index_size varchar(20), unused varchar(20))

    exec('declare tbls CURSOR FOR SELECT name FROM "' + @dbname +

    '"..sysobjects WHERE type = ''U'' AND name LIKE ''' + @tblname + '''')

    OPEN tbls

    while('FETCH IS OK' = 'FETCH IS OK')

    begin

    FETCH next FROM tbls INTO @tablename

    IF @@fetch_status < 0 BREAK

    SELECT @cmdstr = 'use "' + @dbname + '" EXEC sp_spaceused ''' + @tablename + ''''

    INSERT INTO #tblspc exec(@cmdstr)

    IF @@error <> 0

    BEGIN

    DEALLOCATE tbls

    raiserror('Fatal error, unable TO obtain space details FOR tables.', -1, -1)

    return(1)

    END

    end

    DEALLOCATE tbls

    SELECT name AS "Table Name:", rows as "Number OF Rows:" ,

    data AS "Data Size", index_size as "Index Size",

    reserved AS "Allocated Size"

    FROM #tblspc

    ORDER BY convert(int, rows) desc, 1

    return(0)

    GO

    I've created it in Master and call it from QA with the appropriate db chosen

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Hi Allen,

    quote:


    EXEC sp_MSforeachtable 'sp_spaceused ''?'''


    I wasn't aware of this. Thanks!

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • ok a5xo3z1, do i have to create a stored procedure for that? is creating procedures hard or should i just learn to do them? can you tell me how to get what you gave me to show me output?

  • Hi matt1,

    basically, you have to cut and paste the whole code into QA, select the correct db (in my case master) from the combobox and hit F5 to run the code. You should get a response like "Commands completed successfully". To check you can see in EM in your db a stored proc named sp_rpttblspc. In fact you could have also created the proc using EM by selecting to Stored Procedure Entry of your db and then right-click in your right window. Select New Stored Procedure and paste the code there. Both ways do the same. To call the proc go back to QA, select the db you want to check and type EXEC sp_rpttblspc. And once again F5. You should get a resultset for each User table in the chosen db.

    The guy who created the code is a SQL Server MVP with a nice own website. I believe the link to is somewhere on this site.

    Creating Stored Procs isn't very hard, is it?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • no its not but how to you view them in EM?

  • Hi matt1,

    in EM expand your db, click on the Stored Procedure entry and from the list on the right choose your procedure. Double the procedure to see the content.

    Cheers,

    Frank

    Edited by - a5xo3z1 on 05/27/2003 06:02:22 AM

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Hi matt1,

    quote:


    no its not but how to you view them in EM?


    case you've created it in master like me, maybe you have to make master 'visible' by right-clicking on SQL Server registration and checking 'Show system databases and system objects'

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • ah, yes, i created it in one database only, not in master. for now i want to use it in just one database. this is a great stored procedure, thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply