quote:

---

Originally posted by robvolk

are you talking about sys.dm_exec_procedure_stats?

---

SELECT
-- SELECT TOP 100
-- 	[DBName] = DB_NAME(database_id),
-- 	[SCHEMA_NAME] = OBJECT_SCHEMA_NAME(object_id, database_id),
	[last_execution_time]=MAX(S.last_execution_time),
	CASE WHEN OBJECT_NAME(S.object_id, S.database_id) = O.name THEN '' ELSE '*' END,
	[OBJECT_NAME] = OBJECT_NAME(S.object_id, S.database_id)
	, [ProcName] = O.name
--	, * 
-- SELECT TOP 10 *
FROM	sys.dm_exec_procedure_stats AS S
	LEFT OUTER JOIN sys.objects AS O
		 ON O.object_id = S.object_id
		 OR O.name LIKE OBJECT_NAME(S.object_id, S.database_id) + '_HOLD'
WHERE	1=1
	AND S.database_id = 9999	-- My Database ID
--	AND last_execution_time >= DATEADD(day, -1, GetDate())
GROUP BY
	object_name(S.object_id, S.database_id)
	, O.name	
ORDER BY
--	OBJECT_NAME,
	last_execution_time DESC,
	ProcName

SELECT	[last_execution_time]=MAX(last_execution_time),
	CASE WHEN object_name(objectid, dbid) = O.name THEN '' ELSE '*' END,
	object_name(objectid, dbid) as ObjectName
	, [ProcName] = O.name
--	, text
--	, statement_start_offset, objectid
-- List all Database IDs used:	SELECT DISTINCT dbid, [DB Name]=DB_NAME(dbid)
-- SELECT TOP 10 *
FROM	sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)
	LEFT OUTER JOIN sys.objects AS O
		 ON O.object_id = objectid
WHERE	1=1
	AND dbid = 27	-- 9999	-- My Database ID
--	AND last_execution_time >= DATEADD(day, -1, GetDate())
GROUP BY
	object_name(objectid, dbid)
	, O.name	
ORDER BY
	ObjectName
	, last_execution_time DESC

CREATE EVENT SESSION [MissingProcedure] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(package0.callstack,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)
    WHERE ([error_number]=2812))
ADD TARGET package0.event_file(SET filename=N'MissingProc.xel',max_file_size=(5),max_rollover_files=(4)),
ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(1024))
WITH (MAX_MEMORY=1024 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON);
GO
ALTER EVENT SESSION [MissingProcedure] ON SERVER STATE=START;

EXEC No_Such_Procedure

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='MissingProcedure')
    DROP EVENT session MissingProcedure ON SERVER;
GO
CREATE EVENT SESSION [MissingProcedure] ON SERVER 
ADD EVENT sqlserver.error_reported
(
	ACTION
	(
		package0.callstack,
		sqlserver.database_id,
		sqlserver.session_id,
		sqlserver.sql_text,
		sqlserver.tsql_stack
	)
--	WHERE ([error_number]=2812)
	WHERE ([error]=2812)
)
ADD TARGET
--	package0.event_file
	package0.asynchronous_file_target
(
	SET filename=N'MissingProcedure.xel',
	max_file_size=(5),
	max_rollover_files=(4)
),
ADD TARGET package0.ring_buffer
(
	SET
--		max_events_limit=(5000),	-- the customizable attribute, "max_events_limit", does not exist
		max_memory=(1024)
)
WITH (
	MAX_MEMORY=1024 KB,
	EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
	MAX_DISPATCH_LATENCY=120 SECONDS,
	MAX_EVENT_SIZE=0 KB,
	MEMORY_PARTITION_MODE=NONE,
	TRACK_CAUSALITY=OFF,
	STARTUP_STATE=ON
);
GO
ALTER EVENT SESSION [MissingProcedure] ON SERVER STATE=START;

SELECT DISTINCT OC.name
FROM	sys.dm_xe_object_columns AS OC
WHERE	1=1
	AND OC.object_name = 'error_reported'
--
ORDER BY 
	OC.name

CHANNEL
error
ID
KEYWORD
message
severity
state
user_defined
UUID
VERSION

IF NOT (@MyParameter IS NULL)

IF (@MyParameter IS NOT NULL)