Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yhchan2005
Starting Member
26 Posts |
 Posted - 2010-09-28 : 00:29:19
|
| Hi, i have below scrip to return the current cache SP + function execution for SQL Server. But the column CreateBy always return 'dbo',can i return the username who actually create the sp / function ??? Select Distinct ObjectName, ExecutionCount, sp.object_id ObjectID, sp.create_date CreateDate, sp.modify_date ModifyDate, USER_NAME(OBJECTPROPERTY(sp.object_id, 'OwnerId')) CreateBy into #tFinal from ( Select OBJECT_NAME(st.objectid, dbid) ObjectName, max(cp.UseCounts) ExecutionCount, st.objectid 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 cp.objtype = 'proc' and st.dbid = 7 -- Database = PRODUCTION Group By cp.plan_handle, OBJECT_NAME(objectid,st.dbid), st.objectid ) as tResult Left Join sys.objects sp On tResult.ObjectID = sp.object_id Select ROW_NUMBER() OVER(Order By ExecutionCount Desc) AS 'ItemNo', ObjectName, ObjectID, ExecutionCount, CreateDate, ModifyDate, CreateBy from #tFinal |
|
|
TimS
Posting Yak Master
198 Posts |
|
|
|
|
|
|
|
How to get the SP created by who ???