Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    32

    Query showing Tablesize in MB

    Hello all,

    Is there an SQL query that can be run against a database that displays both the number of rows and the physical size of the tables in that database?

    I'm basically after the information displayed in Taskpad view >> Table Info, but in an easy to manipulate and publish format.

    Thanks for your time.

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    The sp_spaceused query gives you the same information that taskpad gives you. If you call it by itself, it gives you a summary of the whole database. If you call it like:

    EXEC sp_spaceused 'owner.table'

    then it gives you the information for a specific table.

    SET NOCOUNT ON

    --Get a summary of the entire database.
    EXEC sp_spaceused

    --Declare needed variables.
    DECLARE
    @max INT,
    @min INT,
    @owner NVARCHAR(256),
    @table_name NVARCHAR(256),
    @sql NVARCHAR(4000)

    DECLARE @table TABLE(
    ident INT IDENTITY(1,1) PRIMARY KEY,
    owner_name NVARCHAR(256),
    table_name NVARCHAR(256))

    IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL
    BEGIN
    DROP TABLE #results
    END

    CREATE TABLE #results(
    ident INT IDENTITY(1,1) PRIMARY KEY, --Will be used to update the owner.
    table_name NVARCHAR(256),
    owner_name NVARCHAR(256),
    table_rows INT,
    reserved_space NVARCHAR(55),
    data_space NVARCHAR(55),
    index_space NVARCHAR(55),
    unused_space NVARCHAR(55))

    --Loop through statistics for each table.
    INSERT @table(owner_name, table_name)
    SELECT
    su.name,
    so.name
    FROM
    sysobjects so
    INNER JOIN sysusers su ON so.uid = su.uid
    WHERE
    so.xtype = 'U'

    SELECT
    @min = 1,
    @max = (SELECT MAX(ident) FROM @table)

    WHILE @min <= @max
    BEGIN

    SELECT
    @owner = owner_name,
    @table_name = table_name
    FROM
    @table
    WHERE
    ident = @min

    SELECT @sql = 'EXEC sp_spaceused ''[' + @owner + '].[' + @table_name + ']'''

    INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space)
    EXEC (@sql)

    UPDATE #results
    SET owner_name = @owner
    WHERE ident = (SELECT MAX(ident) FROM #results)

    SELECT @min = @min + 1
    END

    SELECT * FROM #results
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jan 2004
    Posts
    32
    Thanks Derrick.

    I understand that

    EXEC sp_spaceused

    returns the total for the whole database, and that

    EXEC sp_spaceused 'owner.table'

    returns specific single table information, but I don't understand what the SQL you've supplied does -

    Is that the SQL required to create a procedure that will return the information for all tables in a single database???? (which was what my original question was looking for)

    Thanks

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Yes. Just open that up in Query Analyzer and run it on whatever database you want. It will give you a summary of every table (rows, size, etc.).
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Jan 2004
    Posts
    32
    Fantastic.

    Thank you very much Derrick

  6. #6
    Join Date
    Nov 2009
    Posts
    1

    something a little zippier

    after a little digging thru sp_spaceused, we have:

    --- SQL2000
    select o.name
    , reserved = isnull(sum(reserved), 0)
    , pages = isnull(sum(case when indid in (0, 1) then dpages else used end), 0)
    , IXpages = isnull(sum(case when indid in (0, 1) then used - dpages end), 0)
    , unused = isnull(sum(reserved - used), 0)
    , rows = max(rows)
    from sysobjects o
    join sysindexes i on i.id = o.id
    where i.indid in (0,1,255) and o.xtype = 'U'
    group by o.name order by 3 desc --biggest tables first

    --- SQL2005
    select o.name
    , reservedpages = sum(a.total_pages)
    , usedpages = sum(a.used_pages)
    , pages = sum(case when a.type <> 1 then a.used_pages
    when p.index_id < 2 then a.data_pages else 0 end)
    , rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end)
    from sys.objects o
    join sys.partitions p on p.object_id = o.object_id
    join sys.allocation_units a on p.partition_id = a.container_id
    where o.type = 'U'
    group by o.name
    order by 3 desc -biggest tables first

Posting Permissions

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