asked on
-- insert log_sp_routines (sp_object) select OBJECT_NAME(@@PROCID)
-- for example :
--
-- step 1 - once off event - create a log table
--
create table log_sp_routines (id int identity primary key clustered, sp_object varchar(100), sp_rundate datetime default getdate())
go
--
-- step 2 - once off event - create a sample stored procedure using our "one-liner"
--
create procedure usp_test_sp_log
as
begin
insert log_sp_routines (sp_object) select OBJECT_NAME(@@PROCID)
end
go
--
-- step 3 - execute that stored procedure
--
usp_test_sp_log
go
--
-- step 4 - check out what happened.
--
select * from log_sp_routines
go
-- first up see what traces are running...
SELECT * FROM sys.traces
-- now setup for a new trace
declare @traceid int
declare @max bigint
set @max = 10 -- set at 10 megabytes per trace file, the filecount = 5 says keep / rollover upto 5 of them.
-- Create a new Trace
exec sp_trace_create @traceid output,@options=2,@tracefile=N'c:\audit_files\sql_trace_stored_procedures', @maxfilesize=@max,@stopTime=NULL,@filecount=5
-- Audit SP events eventclass = 42 = SP:Starting might also be interested in 44 = SP:StmtStarting -- see sp_set_event in books online...
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 42, 6, @on -- 6 = NTUserName
exec sp_trace_setevent @TraceID, 42, 7, @on -- 7 = NTDomainName
exec sp_trace_setevent @TraceID, 42, 11, @on -- 11 = Starttime
exec sp_trace_setevent @TraceID, 42, 13, @on -- 13 = Duration
exec sp_trace_setevent @TraceID, 42, 34, @on -- 34 = ObjectName
exec sp_trace_setevent @TraceID, 42, 35, @on -- 35 = DatabaseName
exec sp_trace_setevent @TraceID, 42, 64, @on -- 64 = SessionLoginName
-- Filter out the SQL Profiler events
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- then later on...
declare @traceid int
declare @tracefile varchar(200)
SELECT @traceid=id, @tracefile=path FROM sys.traces WHERE path like '%trace_stored_procedures%' -- or simply select * and fill in the values below for name and traceid
EXEC sp_trace_setstatus @traceid,@status=0 -- 0 = stop, 1 = start, 2 = close and delete trace
SELECT distinct objectname FROM fn_trace_gettable(@tracefile, default) where eventclass = 42
-- then clean up and delete
EXEC sp_trace_setstatus @traceid,@status=2 -- make sure it is stopped first.
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
No, there is no way of doing this. See https://www.experts-exchange.com/questions/23957155/Show-date-stored-procedure-was-last-used-in-ms-sql-2005.html
Thanks,
Lee