Changes to tables

One of my main tasks in my 9 to 5 is migrating data from a third party system to our own database. This will often involve reverse engineering parts of our own application into SQL scripts. One of the main tools I use for this is of course the profiler. I have also found it useful however to capture changes to the number of rows in a table. So for example, if I use the application to add a new company, I can see which tables had new rows added. To do this I perform the following actions.

1. Create a table in the database
2. Populate this table with a list of every table in the database
3. Determine and store the number of rows from each table
4. Perform the action in the applications
5. Determine and store the new number of rows from each table and compare to the previous values
6. Drop the table

So, firstly create the table and populate.

CREATE TABLE [dbo].[tablemon]
(
 TableName VARCHAR(128),
 PreCount INT NULL,
 PostCount INT NULL
)

INSERT INTO [dbo].[tablemon]

SELECT [name], 0, 0 FROM sys.tables ORDER BY [name]

I use the same code to determine and store the number of rows from each table before and after by setting the @preorpost variable to PRE or POST. To get the initial row counts:

DECLARE @preorpost CHAR(4)

DECLARE @tablename VARCHAR(128)
DECLARE @cSQL NVARCHAR(1000)
DECLARE @rowcount INT

SET @preorpost = 'PRE'

IF @preorpost = 'PRE'
 BEGIN
      UPDATE dbo.tablemon SET precount = 0, postcount = 0
 END

DECLARE tablecursor CURSOR FAST_FORWARD FOR SELECT [name] FROM sys.tables ORDER BY [name]
OPEN tablecursor
FETCH NEXT FROM tablecursor INTO @tablename
WHILE @@fetch_status = 0
 BEGIN
      IF @preorpost = 'PRE'
       BEGIN
             SET @cSQL = 'UPDATE dbo.tablemon SET precount = (SELECT COUNT(*) FROM ' + @tablename + ') WHERE tablename = ''' + @tablename + ''''
       END
      ELSE
       BEGIN
            SET @cSQL = 'UPDATE dbo.tablemon SET postcount = (SELECT COUNT(*) FROM ' + @tablename + ') WHERE tablename = ''' + @tablename + ''''
       END
      -- print @cSQL
      EXEC sp_executesql @cSQL
      FETCH NEXT FROM tablecursor INTO @tablename
END
CLOSE tablecursor
DEALLOCATE tablecursor

IF @preorpost = 'POST'

      SELECT * FROM [dbo].[tablemon] WHERE precount <> postcount

Now, after performing the action in the application, change the @preorpost variable to POST and run the code again. You will be presented with a list of the tables who's row counts have changed.



You can now drop the table or if you plan to run the routine again, leave it intact.