Fetch table name,size

  • Hi,

    How can I fetch table name, no of Rows and occupied size in MB from TSQL ?

  • Hi

    Here is a piece of code that I use to accomplish this:

    SET NOCOUNT ON

    --Get a summary of the entire database.

    EXEC sp_spaceused

    --Declare needed variables.

    DECLARE

    @max-2 INT,

    @min-2 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-2 = 1,

    @max-2 = (SELECT MAX(ident) FROM @table)

    WHILE @min-2 <= @max-2

    BEGIN

    SELECT

    @owner = owner_name,

    @table_name = table_name

    FROM

    @table

    WHERE

    ident = @min-2

    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-2 = @min-2 + 1

    END

    SELECT * FROM #results

    DROP TABLE #results

    Hope it helps

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • Nice, thanks Andy.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Andy thank you. this is really great 🙂

  • Your welcome 🙂

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • Thought I'd throw my own version of sp_SpaceUsed out there. Undoubtably, this will need to be updated some time in the near future but it works just fine in 2005 and doesn't require the use of any form of RBAR.

    --_______________________________________________________________________________________________________________________

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

    Purpose:

    Returns a single result set similar to sp_Space used for all user tables at once.

    Notes:

    1. May be used as a view, stored procedure, or table-valued funtion.

    2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.

    Revision History:

    Rev 00 - 22 Jan 2007 - Jeff Moden

    - Initital creation for SQL Server 2000

    Rev 01 - 11 Mar 2007 - Jeff Moden

    - Add automatic page size determination for future compliance

    Rev 02 - 05 Jan 2008 - Jeff Moden

    - Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name

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

    --===== Ensure that all row counts, etc are up to snuff

    -- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should

    -- execute the command below prior to retrieving from the view or UDF. This command takes a while to execute if

    -- you've never run it before but gets much shorter with repeated use.

    DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS

    --===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more

    SELECT DBName = DB_NAME(),

    SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000

    --SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005

    TableName = so.Name,

    TableID = so.ID,

    ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,

    DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

    + SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

    IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB

    - SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

    - SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

    UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB

    - SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,

    Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),

    RowModCtr = MIN(si.RowModCtr),

    HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),

    HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)

    FROM dbo.SysObjects so,

    dbo.SysIndexes si,

    (--Derived table finds page size in KB according to system type

    SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte

    FROM Master.dbo.spt_Values

    WHERE Number = 1 --Identifies the primary row for the given type

    AND Type = 'E' --Identifies row for system type

    ) pkb

    WHERE si.ID = so.ID

    AND si.IndID IN (0, --Table w/o Text or Image Data

    1, --Table with clustered index

    255) --Table w/ Text or Image Data

    AND so.XType = 'U' --User Tables

    AND PERMISSIONS(so.ID) <> 0

    GROUP BY so.Name,

    so.UID,

    so.ID,

    pkb.PageKB

    ORDER BY ReservedKB DESC

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Just like to say thanks for posting these codes. 😀

  • Just tested your code out, Jeff--why does it seem to return a maximum 2048 for the max row size, even in cases where I know the maximum row size of the table is greater than that?

  • paul.knibbs (3/22/2012)


    Just tested your code out, Jeff--why does it seem to return a maximum 2048 for the max row size, even in cases where I know the maximum row size of the table is greater than that?

    Gosh. I've never noticed that before (I've not used the column myself for anything I've done) but you're right. :blush: It uses the XMaxLen column from the legacy dbo.sysindexes column. I'll have to look into it a bit more. I'm a bit shocked because the description for the column from BOL is "Maximum size of a row" but, considering it's from sysindexes documentation, I'm now thinking it's only for index rows (duh! Must have written it without the aid of proper amounts of coffee). That surprises me as well because the use of INCLUDE can make them a lot wider.

    Thanks for pointing this out. I'll remove the columns for future postings.

    {EDIT} I've also removed the columns from the code on this thread so that future readers don't run into a problem. My apologies on this. It's pretty embarassing.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Here's a script I've saved and used from Tibor Karaszi's website. I adapted it slightly to filter table names based on a string at the start of the table name:

    --List all tables in DB, adapted from Tibor Karaszi's SP at: http://www.karaszi.com/SQLServer/util_sp_tableinfo.asp

    --See also sp_spaceused and zzQuery (superseded) for alternative approaches

    --Written by Tibor Karaszi 2010-09-30

    --Modified 2010-10-10, fixed rowcount multiplied by number of indexes.

    --Modified 2010-10-11, fixed rowcount incorrect with BLOB and row overflow data.

    DECLARE @tbl NVARCHAR(128);--sys.tables.name is a SYSNAME data type

    SET @tbl = N'tmpRpt';

    WITH t AS

    (

    SELECT

    SCHEMA_NAME(t.schema_id) AS schema_name

    ,t.name AS table_name

    ,SUM(CASE WHEN p.index_id IN(0,1) AND a.type_desc = 'IN_ROW_DATA' THEN p.rows ELSE 0 END) AS rows

    ,SUM(CAST((a.total_pages * 8.00) / 1024 AS DECIMAL(9,2))) AS MB

    ,SUM(a.total_pages) AS pages

    ,ds.name AS location

    FROM

    sys.tables AS t

    INNER JOIN sys.partitions AS p ON t.OBJECT_ID = p.OBJECT_ID

    INNER JOIN sys.allocation_units AS a ON p.hobt_id = a.container_id

    INNER JOIN sys.data_spaces AS ds ON a.data_space_id = ds.data_space_id

    WHERE (@tbl IS NULL OR t.name LIKE @tbl + '%')

    GROUP BY SCHEMA_NAME(t.schema_id), t.name, ds.name

    )

    SELECT schema_name, table_name, rows, MB, pages, location

    FROM t

    ORDER BY MB DESC, table_name

    Rich

  • Jeff Moden (3/22/2012)

    {EDIT} I've also removed the columns from the code on this thread so that future readers don't run into a problem. My apologies on this. It's pretty embarassing.

    I intended no embarrassment, sorry--was just curious what might cause the effect! Will be interesting to see what the eventual answer to that is.

  • paul.knibbs (3/22/2012)


    Jeff Moden (3/22/2012)

    {EDIT} I've also removed the columns from the code on this thread so that future readers don't run into a problem. My apologies on this. It's pretty embarassing.

    I intended no embarrassment, sorry--was just curious what might cause the effect! Will be interesting to see what the eventual answer to that is.

    Oh no.... no problem. I'd have been pretty embarrassed even if I'd found it on my own and would have posted pretty much the same way. I really thank you for pointing out the flaw!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

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