SQL Server Performance

How to get SP / function created by who ???

Discussion in 'SQL Server 2005 General Developer Questions' started by yhchan2005, Sep 27, 2010.

  1. yhchan2005 Member

    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
  2. satya Moderator

    How many types of SCHEMAS are managed in the databases?
    if none then DBO is only default value returned with that script, you might try looking at INFORMATION_SCHEMA.ROUTINES stored procedure.

Share This Page