This is a possible solution. I am not sure how to avoid it, but I'm sure someone will post another solution that doesn't go RBAR which would definitely be preferable.

CREATE TABLE #TableNames (TableName VARCHAR(100))

CREATE TABLE #RowCounts (TableName VARCHAR(100), NumRows INTEGER)

DECLARE @vSELECT VARCHAR(1000)

DECLARE @vFROM VARCHAR(1000)

DECLARE @vWHERE VARCHAR(1000)

DECLARE @vINSERT VARCHAR(1000)

DECLARE @vTableName VARCHAR(100)

--get all of the tables with a column named RECORD_ModificationDate

INSERT INTO #TableNames(TableName)

SELECT SO.Name

FROM sysobjects SO

WHERE Xtype='U' AND EXISTS( SELECT TOP 1 SC.Name

FROM syscolumns SC

WHERE SC.id=SO.id AND SC.Name='RECORD_ModificationDate')

--the insert and where clauses won't changed based

SET @vINSERT = 'INSERT INTO #RowCounts(TableName, NumRows) '

SET @vWHERE = 'WHERE RECORD_ModificationDate > CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)'

WHILE EXISTS(SELECT TOP 1 * FROM #TableNames)

BEGIN

--get the name of the first table that has not already been counted

SET @vTableName = (SELECT TOP 1 TableName FROM #TableNames)

--update select and from statements

SET @vSELECT = 'SELECT '''+@vTableName+''' AS TableName, COUNT(1) AS NumRows '

SET @vFROM = 'FROM '+@vTableName+' '

--execute the combined insert/select/from/where statements

EXEC (@vINSERT+@vSELECT+@vFROM+@vWHERE)

--remove the table name from the table to signify it has already been counted

DELETE FROM #TableNames WHERE TableName=@vTableName

END

--return the counts

SELECT * FROM #RowCounts

--cleanup after yourself

DROP TABLE #TableNames

DROP TABLE #RowCounts

*edit* I forgot to close the parentheses on the exec