Got stored procedure problems? I feel sorry for you son.
Here's a quick query to pull the cached execution plan out of memory.
I italicized cached because it's not going to stay there forever.
I used this query to find the execution plan of a stored proc that had been running in a SQL job. Instead of pulling down the sproc and trying to estimate the query plan, I chose to see what the cached plan looked like.