n 2005, you can see which queries are in the plan cache, along with the time the plan was put into the cache and the last time it was executed. It's not by any means guaranteed to show you all the queries that have executed, as plans can be removed from the cache.

You can try this query, but as I said, it may not show you all the stored procs run in the last 24 hours, just some of them. Once a plan is removed from the cache, all info on it is lost. If you need this info, look at running a server side trace to capture it.

select last_execution_time, dbid, objectid, text

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle)

where statement_start_offset = 0 and objectid is not null and last_execution_time between getdate() and DATEADD(dd,-1,getdate())

You can use the object_name(objectid) function to get the name from the db id. If you're running SP2, use this query to get the name directly. (object_name was changed in SP2 so that it could take a second parameter)

select last_execution_time, dbid, object_name(objectid,dbid) as ObjectName, text

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle)

where statement_start_offset = 0 and objectid is not null and last_execution_time between getdate() and DATEADD(dd,-1,getdate())