Sean Lange (12/16/2013)
A slight modification to the fine code Luis posted should work for you. I changed the global temp table to instead use a temp table inside the dynamic sql. This may or not work for you. If you need to get at this data for more than just this select you may want to go back to the global temp table.
DECLARE @SQL nvarchar(MAX) = 'create table #Temp(TableName sysname, DateMod datetime, MyRowCount bigint);'
SELECT @SQL = @SQL + 'insert #Temp SELECT ''' + TABLE_NAME + ''' as TableName,
DATEADD( dd, DATEDIFF( dd, 0, _When), 0),
COUNT(*)
FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']
GROUP BY DATEADD( dd, DATEDIFF( dd, 0, _When), 0);'
FROM INFORMATION_SCHEMA.tables
where TABLE_NAME like ('table%') --I added this part to help limit the tables since my test database does not meet your criteria for table structure.
select @SQL = @SQL + 'select * from #Temp;'
exec (@SQL)
There is one mild inconsistency in this code.
The temp table contains "MyRowCount bigint", but "COUNT(*)" is in the SELECT -- it should be "COUNT_BIG(*)" if a bigint result is desired.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".