Professional Web Applications Themes

How to insert count - Microsoft SQL / MS SQL Server

Hi San, The following will use the rowcount from the sysindexes table. That rowcount is not always 100% up to date, so if you want a reliable number you have to run DBCC UPDATEUSAGE(0) before you do the insert. be aware that DBCC UPDATEUSAGE can take a long time to run. insert into tc(tablename, cnt) select so.name, si.rowcnt from sysobjects so INNER JOIN sysindexes si ON so.id = si.id where so.type = 'U' and OBJECTPROPERTY(si.id, 'IsMSShipped') = 0 AND si.indid IN (0,1) -- Jacco Schalkwijk MCDBA, MCSD, MCSE Database Administrator Eurostop Ltd. "San" <com> wrote in message news:0a3501c35dc3$1353a2c0$gbl... ...

  1. #1

    Default Re: How to insert count

    Hi San,

    The following will use the rowcount from the sysindexes table. That rowcount
    is not always 100% up to date, so if you want a reliable number you have to
    run DBCC UPDATEUSAGE(0) before you do the insert. be aware that DBCC
    UPDATEUSAGE can take a long time to run.

    insert into tc(tablename, cnt)
    select so.name, si.rowcnt
    from sysobjects so
    INNER JOIN sysindexes si
    ON so.id = si.id
    where so.type = 'U' and OBJECTPROPERTY(si.id, 'IsMSShipped') = 0
    AND si.indid IN (0,1)

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "San" <com> wrote in message
    news:0a3501c35dc3$1353a2c0$gbl... 


    Jacco Guest

  2. #2

    Default Re: How to insert count

    check this out:

    dbcc updateusage(0)

    create table #tmp (
    name sysname
    , rows int
    , reserved varchar(50)
    , data varchar(50)
    , index_size varchar(50)
    , unused varchar(50)
    )

    exec sp_msforeachtable 'insert #tmp exec sp_spaceused ''?'''

    select * from #tmp order by rows desc

    drop table #tmp

    HTH,
    </wqw>

    "San" <com> wrote in message
    news:0a3501c35dc3$1353a2c0$gbl... 


    Vlad Guest

  3. #3

    Default Re: How to insert count

    Thanks so much Jacco, that really worked..!
    Appreciate that..!
    Cheers ..!
     
    table. That rowcount 
    number you have to 
    aware that DBCC 
    (si.id, 'IsMSShipped') = 0 [/ref]
    is: 
    >
    >
    >.
    >[/ref]
    san Guest

Similar Threads

  1. Question Insert from ASP to SQL using recordset data as values in insert statement
    By JasonM in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 13th, 05:54 PM
  2. Count(*) different than record count
    By Bosconian in forum MySQL
    Replies: 6
    Last Post: October 5th, 11:21 PM
  3. Replies: 3
    Last Post: September 30th, 09:24 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139