SQL Server: Compare row count local and remote for Oracle Linked-Server-Connection

  • Article

As part of an Oracle2SQL-Project, it was required to compare the row count of the destination tables stored in SQL Server and the source tables stored in Oracle. Using transactional replication, the data is continuously replicated from Oracle to SQL Server. Doing so, it is possible to implement a step-by-step-migration, migrating the read-only consumers to SQL Server first.

Result

Statistic about the tables in the current database with different row count compared to an Oracle Database, accessible using Linked Server:

  • ObjectId => for the interested reader, to query some other metadata afterwards
  • TableName => Table- and Schema-Name: [Schema].[Table]
  • RowCountMS => Number of rows in SQL Server
  • RowCountOracle => Number of rows in Oracle, or -1 if not found

T-SQL

 -- ### Replace "MY.REMOTE.ORACLE.DATABASE" with the actual linked server name

-- ### Prepare stats
IF OBJECT_ID('tempdb..#TableStats') IS NOT NULL DROP TABLE #TableStats;

CREATE TABLE #TableStats (
   ObjectId int,
   TableName varchar(100),
   RowCountMS int,
 RowCountOracle int,
);

-- ### Gather stats
PRINT 'Gathering table stats...'
DECLARE @SQLCommand nvarchar(2000) = '
    DECLARE @Pos AS int = CHARINDEX(''.'', ''?'');
   DECLARE @Schema sysname = SUBSTRING(''?'', 2, @Pos - 3);
 DECLARE @Table sysname = SUBSTRING(''?'', @Pos + 2, LEN(''?'') - @Pos - 2);
  DECLARE @SQLOpenQuery nvarchar(1000) = 
      N''DECLARE @x AS int; SELECT @x=1 FROM OPENQUERY([MY.REMOTE.ORACLE.DATABASE], ''''SELECT 1 FROM all_objects WHERE OWNER = '''''''''' + @Schema + '''''''''' AND OBJECT_NAME = '''''''''' + @Table + '''''''''''''')''
    DECLARE @SQLUpdate nvarchar(1000) = 
     N''UPDATE #TableStats SET RowCountOracle = (SELECT vOracle.RowCountOracle FROM OPENQUERY([MY.REMOTE.ORACLE.DATABASE], ''''SELECT COUNT(*) AS RowCountOracle FROM '' + @Schema + ''.'' + @Table + '''''') AS vOracle) WHERE TableName = ''''?'''''';
  -- PRINT @SQLUpdate
  PRINT ''?...''

   INSERT INTO #TableStats (ObjectId, TableName, RowCountMS, RowCountOracle)
    SELECT OBJECT_ID(''?''), ''?'', (SELECT COUNT(*) FROM ?), -1;

    EXEC sp_executeSQL @SQLOpenQuery;
    IF @@RowCount > 0 
    BEGIN
        EXEC sp_executeSQL @SQLUpdate;
   END          
'
PRINT 'EXEC sp_MSforeachtable using the following command:'
PRINT @SQLCommand;
EXEC sp_MSforeachtable @SQLCommand;
PRINT 'Finished!'

-- ### Print stats, RowCountOracle = -1 => Table doesn't exist in Oracle
SELECT * 
FROM #TableStats AS s
WHERE 
-- No SSMA-Objects
s.TableName NOT LIKE '%ssma_oracle%'
-- only tables with primary keys
AND EXISTS (
   SELECT *
  FROM sys.indexes AS idxPK
   WHERE idxPK.object_id = s.ObjectId
    AND idxPK.is_primary_key = 1
)
-- only differences
AND s.RowCountMS <> s.RowCountOracle
ORDER BY s.TableName;

Further Reading