Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Unanswered: Rowcount in tables

    Can someone throw light on how to get the rowcount of a table that is stored in any system tables? I want to get tablename and rowcount for all user tables in a database in a query. Is there anyway other than count(*)?

    Thanks
    Vinnie

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DBCC UPDATEUSAGE (0) WITH COUNT_ROWS
    
    SELECT rows, Object_name(id)
       FROM dbo.sysindexes
       WHERE  indid IN (0, 1)
    If you skip the DBCC, you'll get a SWAG guess, but none too accurate in a busy server.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    sysindexes has a rowcount, see BOL for more details on this.

    EDIT: sniped again!

  4. #4
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134
    Thanks guys.

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Check for sp_spaceused. It may help you.

  6. #6
    Join Date
    Jul 2004
    Posts
    60
    Here's my little addtion to the pile: (built on MSSQL2k)

    DECLARE @MinSize dec(28,2), @LikeName varchar(45), @SizeSort bit, @IncludeLogs bit
    --DBCC UPDATEUSAGE(0) WITH COUNT_ROWS

    SET @MinSize = .00
    SET @LikeName = '' --Company%'
    SET @SizeSort = 1
    SET @IncludeLogs = 0

    /*
    ** We need to create a temp table to do the calculation.
    ** reserved: sum(reserved) where indid in (0, 1, 255)
    ** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)

    ** indexp: sum(used) where indid in (0, 1, 255) - data
    ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
    */
    IF @LikeName = '' SET @LikeName = '%'

    CREATE TABLE #space
    (
    name varchar(30),
    id int,
    type char(1),
    rows int NULL,
    reserved dec(28,2) NULL,
    data dec(28,2) NULL,
    datapages dec(28,2) NULL,
    blob dec(28,2) NULL,
    indexp dec(28,2) NULL,
    unused dec(28,2) NULL,
    rowsize dec(28,2) NULL
    )

    SET NOCOUNT ON

    /************************************************** ***********
    ** Generate a list of all User and System tables.
    ** AND
    ** Now calculate the summary data.
    ** reserved: sum(reserved) where indid in (0, 1, 255)
    */
    INSERT INTO #space (name, id, type, reserved)
    SELECT LEFT(obj.name,30), obj.id, obj.type, SUM(reserved)
    FROM sysindexes idx, sysobjects obj
    WHERE idx.id = obj.id
    AND idx.indid IN (0, 1, 255)
    AND obj.type in ('S','U')
    AND obj.name != 'syslogs'
    AND obj.name LIKE @LikeName
    AND (@IncludeLogs = 1 OR obj.name NOT LIKE '%Log')
    GROUP BY obj.name, obj.id, obj.type

    /************************************************** ***********
    ** Initialize these to zero.
    */
    UPDATE #space
    SET rows = 0, data = 0, blob = 0, indexp = 0, unused = 0

    /************************************************** ***********
    ** data: sum(dpages) where indid < 2
    ** + sum(used) where indid = 255 (text)
    */
    UPDATE #space
    SET data = data + ISNULL((
    SELECT SUM(idx.dpages)
    FROM sysindexes idx
    WHERE id = spc.id
    AND idx.indid < 2), 0)
    FROM #space spc

    UPDATE #space
    SET blob = blob + ISNULL((
    SELECT SUM(idx.used)
    FROM sysindexes idx
    WHERE id = spc.id
    AND idx.indid = 255), 0)
    FROM #space spc

    /************************************************** ***********
    ** index: sum(used) where indid in (0, 1, 255) - data space
    */
    UPDATE #space
    SET indexp = ISNULL((
    SELECT SUM(idx.used)
    FROM sysindexes idx
    WHERE id = spc.id
    AND idx.indid IN (0, 1, 255)), 0) - data - blob
    FROM #space spc

    /************************************************** ***********
    ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
    */
    UPDATE #space
    SET unused = reserved - ISNULL((
    SELECT SUM(idx.used)
    FROM sysindexes idx
    WHERE id = spc.id
    AND idx.indid IN (0, 1, 255)), 0)
    FROM #space spc

    /************************************************** ***********
    ** rows: rows where indid < 2
    */
    UPDATE #space
    SET rows = idx.rows
    FROM #space spc, sysindexes idx
    WHERE spc.id = idx.id
    AND idx.indid < 2

    /************************************************** ***********
    ** Page Size: for Windows NT
    */
    DECLARE @PageSize int, @UsablePage int
    SELECT @PageSize = low
    FROM master.dbo.spt_values
    WHERE number = 1 AND type = 'E'

    set @UsablePage = @PageSize - 132

    /************************************************** ***********
    ** Compute the results
    */
    UPDATE #space SET
    reserved = reserved * @PageSize,
    datapages = data,
    data = data * @PageSize,
    blob = blob * @PageSize,
    indexp = indexp * @PageSize,
    unused = unused * @PageSize

    UPDATE #space SET
    rowsize =
    CASE
    WHEN rows < 50 THEN -1
    ELSE ((data) / rows)
    END

    UPDATE #space SET
    reserved = reserved / 1024.0 / 1024.0,
    data = data / 1024.0 / 1024.0,
    blob = blob / 1024.0 / 1024.0,
    indexp = indexp / 1024.0 / 1024.0,
    unused = unused / 1024.0 / 1024.0

    /************************************************** ***********
    ** Finally: output the report header

    */

    PRINT GETDATE()
    PRINT ''

    IF @MinSize != 0
    PRINT 'Tables with a Total space used of ' + LTRIM(STR(@MinSize, 8,1)) + ' MB or greater in the ' + DB_NAME() + ' database.'
    ELSE
    PRINT 'All tables in the ' + DB_NAME() + ' database.'

    PRINT ''


    /************************************************** ***********
    ** Finally: output the totals
    */

    DECLARE @DataTotal dec(28,2), @BlobTotal dec(28,2), @IndexTotal dec(28,2), @Format varchar(15)

    SELECT @DataTotal = SUM(data),
    @BlobTotal = SUM(blob),
    @IndexTotal = SUM(indexp)
    FROM #space

    SET @Format = CONVERT(varchar(15),convert(money,@DataTotal+@Blob Total+@IndexTotal),1)
    PRINT 'Space used : ' + REPLICATE(' ',15-DATALENGTH(@Format)) + @Format + ' MB'
    SET @Format = CONVERT(varchar(15),convert(money,@DataTotal),1)
    PRINT 'Space used by table data : ' + REPLICATE(' ',15-DATALENGTH(@Format)) + @Format + ' MB'
    SET @Format = CONVERT(varchar(15),convert(money,@BlobTotal),1)
    PRINT 'Space used by text/image data : ' + REPLICATE(' ',15-DATALENGTH(@Format)) + @Format + ' MB'
    SET @Format = CONVERT(varchar(15),convert(money,@IndexTotal),1)
    PRINT 'Space used by table indexes : ' + REPLICATE(' ',15-DATALENGTH(@Format)) + @Format + ' MB'
    SET @Format = CONVERT(varchar(15),@PageSize)
    PRINT 'Page size : ' + REPLICATE(' ',15-DATALENGTH(@Format)) + @Format + ' Bytes'
    SET @Format = CONVERT(varchar(15),@UsablePage)
    PRINT 'Usable Page size : ' + REPLICATE(' ',15-DATALENGTH(@Format)) + @Format + ' Bytes'
    PRINT ''

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

    ** Finally: output the detail
    */


    update #space
    set rowsize = CASE
    WHEN rowsize < 0 THEN 0
    WHEN (data) < .1 THEN 0
    ELSE rowsize
    END

    SELECT
    TableName = name,
    Rows = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,rows),1)),
    Total = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,data+blob+indexp),1)),
    Data = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,data),1)),
    Blob = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,blob),1)),
    Indexes = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,indexp),1)),
    RowBytes = convert(varchar(11),
    CASE
    WHEN rowsize = 0 THEN 'n/a'
    ELSE CONVERT(varchar(15),convert(money,rowsize),1)
    END),
    RowsPage = convert(varchar(11),
    CASE
    WHEN rows = 0 THEN 'n/a'
    ELSE CONVERT(varchar(15),convert(money,rows/datapages),1)
    END),
    Pages = convert(varchar(11),CONVERT(varchar(15),convert(mo ney,datapages),1)),
    TotalSize = data+indexp+blob
    INTO #report
    FROM #space
    WHERE data+indexp >= @MinSize AND type = 'U' and name != 'dtproperties'

    UPDATE #report
    SET Rows = REPLACE(REPLACE(Rows,'.00',''),'.0',''),
    Pages = REPLACE(REPLACE(Pages,'.00',''),'.0','')

    UPDATE #report
    SET Rows = REPLICATE(' ',11-DATALENGTH(Rows)) + Rows,
    Total = REPLICATE(' ',11-DATALENGTH(Total)) + Total,
    Data = REPLICATE(' ',11-DATALENGTH(Data)) + Data,
    Blob = REPLICATE(' ',11-DATALENGTH(Blob)) + Blob,
    Indexes = REPLICATE(' ',11-DATALENGTH(Indexes)) + Indexes,
    RowsPage = REPLICATE(' ',11-DATALENGTH(RowsPage)) + RowsPage,
    Pages = REPLICATE(' ',11-DATALENGTH(Pages)) + Pages,
    RowBytes = REPLICATE(' ',11-DATALENGTH(RowBytes)) + RowBytes


    PRINT ' ------- Average ------- -------------- Table Space In MB --------------'

    IF @SizeSort = 1
    SELECT
    TableName,
    ' Row Size' = RowBytes,
    ' Rows/Page' = RowsPage,
    ' Data Pages' = Pages,
    ' Rows' = Rows,
    ' Total' = Total,
    ' Data' = Data,
    ' Text/Img' = Blob,
    ' Index' = Indexes
    FROM #report
    ORDER BY TotalSize DESC
    ELSE
    SELECT
    TableName,
    ' Row Size' = RowBytes,
    ' Rows/Page' = RowsPage,
    ' Data Pages' = Pages,
    ' Rows' = Rows,
    ' Total' = Total,
    ' Data' = Data,
    ' Text/Img' = Blob,
    ' Index' = Indexes
    FROM #report
    ORDER BY TableName

    PRINT ''

    DROP TABLE #space
    DROP TABLE #report

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by oddity
    Here's my little addtion to the pile: (built on MSSQL2k)
    Great zot there, bubba! I'd consider that a pile all its own, not an addition to an existing pile! Whew, I can't wait to see a major contribution!

    -PatP

  8. #8
    Join Date
    Sep 2003
    Posts
    364
    I got the following errors when I ran this in Query Analyzer:

    Server: Msg 137, Level 15, State 2, Line 172
    Must declare the variable '@Blob'.
    Server: Msg 170, Level 15, State 1, Line 201
    Line 201: Incorrect syntax near 'ney'.

  9. #9
    Join Date
    Jul 2004
    Posts
    60
    i think you might have a simple wrapping issue...@blob isnt a variable in this script but @blobtotal is.


    testing it...that is exactly what happened...

    any tips on posting this without having the format chang on me?

  10. #10
    Join Date
    Jul 2004
    Posts
    60
    try the attachment...

    for some reason pasting it FROM this forum adds some freaky yeaky spaces.

    they dont appear as spaces in the forum window, but they sure as hell get added if you copy/paste it out of here....

    ...sorry...forum noooob issue.
    Attached Files Attached Files

  11. #11
    Join Date
    Sep 2003
    Posts
    364
    Cool that worked. Check this script out that I've been using.
    Attached Files Attached Files

Posting Permissions

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