Compare rowcounts in two SQL tables with the same schema. Note: tables must have one or more primary keys.

CompareTables.sql

DECLARE @OldDatabase varchar(50)
DECLARE @NewDatabase varchar(50)

SELECT @OldDatabase = '4-22-NS'
SELECT @NewDatabase = '4-25-NS'

;WITH tableSchema AS (
	SELECT k.* FROM information_schema.columns c
	INNER JOIN information_schema.key_column_usage k
	ON c.column_name = k.column_name
	AND c.table_name = k.table_name	
), firstPK AS (
	SELECT table_name, column_name as 'primarykey'
	FROM tableSchema
	WHERE ordinal_position = 1
)
SELECT DISTINCT 'SELECT ''' + c.table_name + ''' AS TableName, COUNT(1) AS RowCountDiff' + CHAR(13) +
'FROM [' + @NewDatabase + '].dbo.' + c.table_name + ' new ' + CHAR(13) +
'LEFT JOIN [' + @OldDatabase + '].dbo.' + c.table_name + ' old ' + CHAR(13) + 
STUFF(( 
	SELECT ' AND new.' + c2.column_name + ' = old.' + c2.column_name
	FROM tableSchema c2
	WHERE c2.TABLE_NAME = c.TABLE_NAME
	GROUP BY ' AND new.' + c2.column_name + ' = old.' + c2.column_name
	FOR XML PATH('')	
	), 1, 5, 'ON '
)  + CHAR(13) + 'WHERE old.' + f.primarykey + ' IS NULL;' + CHAR(13)
FROM tableSchema c
INNER JOIN firstPK f
ON c.table_name = f.table_name