[SOLVED] Join queries from sp

Join queries from sp_MSForEachTable

I'm using the following query to get the times when a table was last updadted by users:

EXEC sp_MSForEachTable 'SELECT ''?'' as TableName, 
                                  last_user_update,
                                  user_updates,
                                  index_id
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID(''SP3D_DB_RESEARCH_MDB'') AND 
                    OBJECT_ID = OBJECT_ID(''?'')'

But in the results window I get like a table with one row for each result. Is there any way to join them as an unique query so I can sort the results?

I'm using SQL Server 2008 R2 and Microsoft SQL Server Management Studio.

Solution

You can not JOIN with the procedure but you will be able to get the result into one (temp)Table(variable), which you might use for joins in a second step using INSERT INTO with the EXEC. e.g.

Declare @Collect Table (Name Varchar(100),cnt integer)
Insert into @Collect
EXEC sp_MSForEachTable 'SELECT ''?'' as TableName,Count(*) from ?' 
Select * from @Collect