[SQL2k5] Dynamic SQL Query Select on all user tables

In one query, I would like to query every user table in a specified database for

SELECT TOP (3) COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
GROUP BY [Target IP]
ORDER BY EventNum DESC

How is this possible?

Please give examples, I am a beginner.

Assume every table has the same structure for columns event count, target ip, and time.

Forgot, for every table it selects from, the table name must be one of the returned columns.|||

Hi, try the following:

sp_msforeachtable 'SELECT *,''?'' AS TableName FROM (SELECT TOP (3) , COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
FROM ?
GROUP BY [Target IP]
ORDER BY EventNum DESC) SubQuery'

But keep in mind that this should be just used for administrative task and adhoc queries not for business logic as the procedure is not documented, therefore not supported and could be deprecated in the next version of SQL Server.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de