Post reply

Need to query row counts for only some tables

  • January 22, 2010 at 9:55 am

    #214789

    What I am trying to do is select from one table (BWCheck) that has a list of tables in it and get row counts for those tables and then insert them into that same table where the table names match.

    What this will accomplish is we are using a 3rd party software to maintaing synchronization of our data between sql and our legacy db server

    I need to check the row counts from the legacy server with the row counts for the SQL server.

    Is this possible to do?

  • January 22, 2010 at 12:05 pm

    #1107497

    Try this

    -- Shows all user tables and row counts for the current database --

    --===Remove OBJECTPROPERTY function call to include system objects

    SELECT o.NAME, i.rowcnt FROM sysindexes AS i

    INNER JOIN sysobjects AS o

    ON i.id = o.id WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0

    ORDER BY o.NAME

    The above is NOT my code, but rather an article written many years ago here on SSC by an author whose name I have forgotten - may he/she pardon me for not quoting them to give the credit deserved

    You could run the above query to place the results into a local temporary table and then update your permanent table using a join to the temp table.

    Here is another video to demonstrate the sp_foreachtable

    http://qa.sqlservercentral.com/articles/Video/66317/

    by Andy Warren

    sp_msforeachtable 'SELECT ''?'', COUNT(*) FROM ?'

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

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

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