mrdenny, Sorry but you are flat wrong on this one, SQL Profiler will not provide you with any information about this.
JohnTcolo, yes that is a starting point, although I was looking for something a little further.
This is what I have done to solve this issue:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblSprocNotUsed](
[SystemRestartDate] [datetime] NULL,
[ScriptRunDate] [datetime] NULL,
[DBName] [varchar](128) NULL,
[OBJECTID] [int] NULL,
[sproc_name] [varchar](128) NULL,
[run_count] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'cp_unusedsprocs')
BEGIN
DROP Procedure cp_unusedsprocs
END
GO
CREATE PROCEDURE dbo.cp_unusedsprocs
AS
SET NOCOUNT ON
DECLARE @sql VARCHAR(1000)
, @NAME SYSNAME
, @sproc_name SYSNAME
, @SysRestartDate DATETIME
DECLARE cr_dbs CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
FOR READ ONLY
OPEN cr_dbs
FETCH NEXT FROM cr_dbs INTO @NAME
SELECT @SysRestartDate = create_date FROM sys.databases WHERE name = 'tempdb'
IF DATEDIFF(dd, @SysRestartDate, GETDATE())< 2
BEGIN
TRUNCATE TABLE tblSprocNotUsed
END
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = "USE ["+ @NAME +"] INSERT tblSprocNotUsed (SystemRestartDate, ScriptRunDate, DBName, SPROC_NAME, OBJECTID) SELECT '"+cast(@SysRestartDate AS VARCHAR(30)) +"', '"+ CAST(GETDATE()AS VARCHAR(30)) +"', '"+ @NAME +"', o.name,
bject_id FROM sys.objects O WHERE type = 'P' AND o.name NOT LIKE 'dt_%' AND o.name NOT LIKE 'cf_%' AND o.name NOT IN (SELECT SPROC_NAME FROM tblSprocNotUsed WHERE DBName = '"+ @NAME +"' )"
EXEC (@sql)
FETCH NEXT FROM cr_dbs INTO @NAME
END
CLOSE cr_dbs
DEALLOCATE cr_dbs
UPDATE tblSprocNotUsed
SET run_count = ISNULL(Execution_count, 0)
, SystemRestartDate = @SysRestartDate
, ScriptRunDate = GETDATE()
FROM (SELECT DB_NAME(st.DBID) DBName
, OBJECT_SCHEMA_NAME(st.objectid,DBID) SchemaName
, OBJECT_NAME(st.objectid,DBID) StoredProcedure
, ISNULL(MAX(cp.usecounts), 0) Execution_count
FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.DBID) IS NOT NULL
AND DB_NAME(st.DBID) NOT IN ('master', 'msdb', 'tempdb')
AND cp.objtype = 'proc'
GROUP BY cp.plan_handle
, DB_NAME(st.DBID)
, OBJECT_SCHEMA_NAME(objectid,st.DBID)
, OBJECT_NAME(objectid,st.DBID)
-- ORDER BY MAX(cp.usecounts)
) C
WHERE tblSprocNotUsed.DBName = C.DBName AND sproc_name = C.StoredProcedure
GO
exec cp_unusedsprocs
GO
SELECT DBName, OBJECTID, sproc_name, ISNULL(run_count, 0) run_count, SystemRestartDate, ScriptRunDate
FROM tblSprocNotUsed
WHERE run_count = 0
GROUP BY DBName, sproc_name, SystemRestartDate, OBJECTID, ScriptRunDate, run_count
ORDER BY DBName, run_count DESC, sproc_name
GO
AL Almeida
Senior DBA
"May all those that come behind us, find us faithfull"
