I have two databases, the structures should be exactly the same. One holds January data and the other database holds February data. We have a script that checks one against the other and lists the tables and the row count in each one versus the previous month.
However we are getting up certai tables which though they are coming up are showing 0 row count whenin fact they are populated. Sysobjects shows them as does the SP_TABLE etc commands.
** If there is an alternative way PLEASE - thanks **
Code is below
/** This IS to check whether a table that existed in a previous database is also in the latest one AND
it runs a count of records in the prior and new databases and calculates the difference.
It also allows you to set the database names/locations as a parameter. **/
-- YOU MUST enter in the latest Database and the previous database
DECLARE @NEW_DB VARCHAR(60)
DECLARE @PRIOR_DB VARCHAR(60)
DECLARE @SQL VARCHAR(2000)
-- Manual Entries HERE
SET @NEW_DB = 'FEBRUARY_DBF'
SET @PRIOR_DB = JANUARY_DBF'
SET @SQL = 'select *, CASE WHEN NEW <> ISNULL(PRIOR, ''X'') THEN ''MISSING''
ELSE ''OK'' END as ''Table Exists'',
NEW_Count-PRIOR_Count as ''Diff Count''
FROM
(select a.name NEW ,
(SELECT b.name from '+@PRIOR_DB+'.dbo.sysobjec
ts b where b.name = a.name) PRIOR, isnull((SELECT rows FROM '+@NEW_DB+'.dbo.sysindexes
WHERE name = a.name AND indid < 2),0) ''NEW_count'', isnull((SELECT rows FROM '+@PRIOR_DB+'.dbo.sysindex
es
WHERE name = a.name AND indid < 2),0) ''PRIOR_count''
FROM '+@NEW_DB+'.dbo.sysobjects
a
where xtype in (''U'',''V'') ) t
order by 5,1'
PRINT @SQL
EXEC(@SQL)
Start Free Trial
