Results 1 to 4 of 4

Thread: GETTING ROW COUNTS FROM DATABASE

  1. #1
    DIANNE Guest

    GETTING ROW COUNTS FROM DATABASE


    I need a procedure that will go to a database and give me all the row counts for the user tables.

    Does anyone know how I can get this?

    Thanks,
    Dianne

  2. #2
    Larry Guest

    GETTING ROW COUNTS FROM DATABASE (reply)

    Dianne,

    I had a script somewhere, but can't seem to find it (changed jobs recently). Anyway, here's a "quick and dirty" query that seems to work, but of course there are probably other (better) ways to get it done. Remember MS says to never query system tables directly, but....

    select distinct SUBSTRING(so.name,1,30) AS name , si.rows
    from sysindexes si, sysobjects so
    where si.id = so.id
    and si.name = so.name
    and so.type = "u"
    and so.status > 0

    Hope this helps.
    - Larry


    ------------
    DIANNE at 11/15/00 1:35:20 PM


    I need a procedure that will go to a database and give me all the row counts for the user tables.

    Does anyone know how I can get this?

    Thanks,
    Dianne

  3. #3
    Gary Andrews Guest

    GETTING ROW COUNTS FROM DATABASE (reply)

    Following is code got from someone else and modified to show all tables, instead
    of the top 25 tables. You need to modiy the USE statement prior to execution.

    /************************************************** ************************************
    *
    * BigTables.sql
    * Bill Graziano (SQLTeam.com)
    * graz@sqlteam.com
    * v1.1
    *
    ************************************************** ************************************/
    -- list rows in tables in a database g.andrews 8/8/00
    --
    use msdb -- <--------------------modify as appropriate

    declare @id int
    declare @type character(2)
    declare @pages int
    declare @dbname sysname
    declare @dbsize dec(15,0)
    declare @bytesperpage dec(15,0)
    declare @pagesperMB dec(15,0)

    create table #spt_space
    (
    objid int null,
    rows int null,
    reserved dec(15) null,
    data dec(15) null,
    indexp dec(15) null,
    unused dec(15) null
    )

    set nocount on

    -- Create a cursor to loop through the user tables
    declare c_tables cursor for
    select id
    from sysobjects
    where xtype = &#39;U&#39;

    open c_tables

    fetch next from c_tables
    into @id

    while @@fetch_status = 0
    begin

    /* Code from sp_spaceused */
    insert into #spt_space (objid, reserved)
    select objid = @id, sum(reserved)
    from sysindexes
    where indid in (0, 1, 255)
    and id = @id

    select @pages = sum(dpages)
    from sysindexes
    where indid < 2
    and id = @id
    select @pages = @pages + isnull(sum(used), 0)
    from sysindexes
    where indid = 255
    and id = @id
    update #spt_space
    set data = @pages
    where objid = @id


    /* index: sum(used) where indid in (0, 1, 255) - data */
    update #spt_space
    set indexp = (select sum(used)
    from sysindexes
    where indid in (0, 1, 255)
    and id = @id)
    - data
    where objid = @id

    /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
    update #spt_space
    set unused = reserved
    - (select sum(used)
    from sysindexes
    where indid in (0, 1, 255)
    and id = @id)
    where objid = @id

    update #spt_space
    set rows = i.rows
    from sysindexes i
    where i.indid < 2
    and i.id = @id
    and objid = @id

    fetch next from c_tables
    into @id
    end


    select
    Table_Name = (select left(name,25) from sysobjects where id = objid),
    rows = convert(char(11), rows),
    reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + &#39; &#39; + &#39;KB&#39,
    data_KB = ltrim(str(data * d.low / 1024.,15,0) + &#39; &#39; + &#39;KB&#39,
    index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + &#39; &#39; + &#39;KB&#39,
    unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + &#39; &#39; + &#39;KB&#39

    from #spt_space, master.dbo.spt_values d
    where d.number = 1
    and d.type = &#39;E&#39;
    order by reserved desc

    drop table #spt_space
    close c_tables
    deallocate c_tables

    HTHs Gary
    ------------
    DIANNE at 11/15/00 1:35:20 PM


    I need a procedure that will go to a database and give me all the row counts for the user tables.

    Does anyone know how I can get this?

    Thanks,
    Dianne

  4. #4
    Venu Guest

    GETTING ROW COUNTS FROM DATABASE (reply)

    Hi Dianne,

    Here is the stored procedure to count the rows

    Create Procedure Count_rows
    as
    Declare @count numeric(8)
    Declare @Tab_name varchar(30)
    Declare test_cursor Cursor for
    Select Name from sysobjects where xtype = &#39;U&#39;
    open test_cursor
    fetch next from test_cursor
    into @Tab_name
    While @@fetch_status = 0
    Begin
    Print @Tab_Name
    Exec(&#39;Select count(*) from &#39;+@Tab_name)
    Fetch next from test_cursor
    into @Tab_name
    end
    Close test_cursor
    Deallocate test_cursor

    Hope This helps
    Venu
    ------------
    Larry at 11/15/00 1:54:38 PM

    Dianne,

    I had a script somewhere, but can&#39;t seem to find it (changed jobs recently). Anyway, here&#39;s a &#34;quick and dirty&#34; query that seems to work, but of course there are probably other (better) ways to get it done. Remember MS says to never query system tables directly, but....

    select distinct SUBSTRING(so.name,1,30) AS name , si.rows
    from sysindexes si, sysobjects so
    where si.id = so.id
    and si.name = so.name
    and so.type = &#34;u&#34;
    and so.status > 0

    Hope this helps.
    - Larry


    ------------
    DIANNE at 11/15/00 1:35:20 PM


    I need a procedure that will go to a database and give me all the row counts for the user tables.

    Does anyone know how I can get this?

    Thanks,
    Dianne

Posting Permissions

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