question

something like this:

DECLARE @SQL nvarchar(4000);

WITH tableName AS (

SELECT [value] ='myTable1'UNIONALL

SELECT'myTable2'UNIONALL

SELECT'myTable3'UNIONALL

SELECT'myTable4'UNIONALL

SELECT'myTable5'UNIONALL

SELECT'myTable6'UNIONALL

SELECT'myTable7'UNIONALL

SELECT'myTable8'UNIONALL

SELECT'myTable9'UNIONALL

SELECT'myTable10'

)

SELECT @SQL =COALESCE(@SQL,'')+N'SELECT

[tableName] = '''+tableName.value+''',

[records] = COUNT(*),

[yourField]

FROM [dbo].['+tableName.value+']

WHERE 1=1 --replace with condition here

GROUP BY [yourField]

UNION

'

FROM tableName;

--chop off the last union and add an order by

SET @SQL =SUBSTRING(@SQL,1,LEN(@SQL)-7)+N'ORDER BY tableName,records';

--this is just to verify your code, you may have to adjust the substring above to chop out the last UNION correctly

SELECT @SQL;

--uncomment this to run the query once you're happy with it

--EXECUTE sp_executesql @SQL;