January 22, 2010 at 9:55 am
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
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://www.sqlservercentral.com/articles/Video/66317/
by Andy Warren
sp_msforeachtable 'SELECT ''?'', COUNT(*) FROM ?'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply