Query to find plan details for a specific stored procedure, searching by name
Given that the sys.dm_exec_query_stats DMV returns data for the entire instance, you need to be able to get object Schema and name info across all Databases. Using ST.objectid = OBJECT_ID(N'dbo.ProcedureName'), as has been suggested, is error-prone since it will only resolve the name relative to the database in which the query is being executed:


If the object name does not exist in that Database, then it will return NULL, which will filter out all rows.
If the object name does exist in that Database but you are actually wanting that object name from a different database, then you will get an object_id value that might be correct, but could just as well point to a different object and might return one or more incorrect rows.
If an object_id value is returned, whether correct or incorrect, that particular value can exist across multiple databases and so you might get multiple rows back for different objects in different databases.


At the very least, you would need to provide a fully-qualified object name (i.e. three-part name). However, even if you do use OBJECT_ID(N'DatabaseName.SchemaName.ObjectName') the query still has the potential to run into problem #3 noted above since that object_id, even being correct, can exist across multiple Databases. So you need a second condition using the DB_ID() function to narrow down to the intended Database.

WHERE st.[objectid] = OBJECT_ID(N'DatabaseName.SchemaName.ObjectName')
AND   st.[dbid] = DB_ID(N'DatabaseName')


HOWEVER, my preference is to use the OBJECT_NAME and OBJECT_SCHEMA_NAME functions as they both accept an optional 2nd parameter for database_id. The advantage here is that you can use them in the SELECT clause when you are not narrowing down to one specific object, so that you can see the object names for all of the rows in the DMV. It also allows for filtering down to the same object across multiple Databases, which is useful when you have the same Stored Procedure in multiple Databases and want to see rows for it across all Databases, even though it likely has different object_id values across those Databases.

Hence, you could optionally add the following to the SELECT clause (I use this when Cross Applying sys.dm_exec_sql_text to DMVs such as sys.dm_exec_requests, etc):

DB_NAME(st.[dbid]) AS [DatabaseName],
OBJECT_SCHEMA_NAME(st.[objectid], st.[dbid]) AS [SchemaName],
OBJECT_NAME(st.[objectid], st.[dbid]) AS [ObjectName]


And you would then update your query to have the following WHERE clause:

WHERE OBJECT_NAME(st.[objectid], st.[dbid]) = N'my_proc_name'
AND   OBJECT_SCHEMA_NAME(st.[objectid], st.[dbid]) = N'dbo' -- or whatever schema
-- AND  DB_NAME(st.[dbid]) = N'DatabaseName' -- optionally narrow down to specific DB