Post reply

counting records in tables

  • September 1, 2003 at 4:41 am

    #59360

    I am having problems with counting records on a number of table in a Database.

    I have created this script.

    CREATE TABLE TBLSize1

    (Tblname varchar(80),

    TblRows int,

    TblReserved varchar(80),

    TblData varchar(80),

    TblIndex_Size varchar(80),

    TblUnused varchar(80))

    DECLARE @DBname varchar(80)

    DECLARE @tablename varchar(80)

    SELECT @DBname = DB_NAME(DB_ID())

    PRINT 'User Table size Report for (Server / Database): ' + @@ServerName + ' / ' + @DBName

    PRINT ''

    PRINT 'By Size Descending'

    DECLARE TblName_cursor CURSOR FOR

    SELECT NAME

    FROM sysobjects

    WHERE xType = 'U'

    OPEN TblName_cursor

    FETCH NEXT FROM TblName_cursor

    INTO @tablename

    WHILE @@FETCH_STATUS = 0

    BEGIN

    INSERT INTO TBLSize1(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)

    EXEC Sp_SpaceUsed @tablename

    -- Get the next table.

    FETCH NEXT FROM TblName_cursor

    INTO @tablename

    END

    CLOSE TblName_cursor

    DEALLOCATE TblName_cursor

    SELECT CAST(Tblname as Varchar(30)) 'Table',

    CAST(TblRows as Varchar(14)) 'Row Count',

    CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',

    CAST(TblData as Varchar(14)) 'Data Space',

    CAST(TblIndex_Size as Varchar(14)) 'Index Space',

    CAST(TblUnused as Varchar(14)) 'Unused Space'

    FROM TBLSize1 where tblname in (

    'table_1',

    'table_2',

    'table_3',

    'table_4',

    'table_5',

    'ETC')

    Order by Tblname

    It is putting table counts into a table and I am then using this info. I have found out lately that the table counts are sometimes different to when I manually run the ‘select count(*) from table_1’. How does the ‘Sp_SpaceUsed’ SP work? Is this a problem with updating Stats.

    If anyone needs anymore info please ask.

    Thanks in advance

  • September 1, 2003 at 6:39 am

    #472096

    Check DBCC UPDATEUSAGE in BOL.

    CVM.

  • September 1, 2003 at 6:52 pm

    #472135

    Hi there

    This routines used are unreliable. The updateusage command is a good one before your routine (in a quiet period). Also take a look at sysindexes and the row column, see BOL for help. I believe the scripts section of this site has lots of table row count examples to rip 🙂

    Cheers

    Ck

    Chris Kempster

    http://www.chriskempster.com

    Author of "SQL Server 2k for the Oracle DBA"


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

Viewing 3 posts - 1 through 2 (of 2 total)

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