usp_SQL_In_sp_who2

  • Brandon Forest

    SSCommitted

    Points: 1811

    Comments posted to this topic are about the item usp_SQL_In_sp_who2

  • lbabs

    SSC Journeyman

    Points: 80

    Very cool. But it would be nice to get rig of the cursor and replace with Min/Max looping logic. Otherwise this is an excellent utility.

  • Alan Vogan

    SSCrazy

    Points: 2483

    Nice surf. 3 of us took a 2 hour brunch on Tuesday... 😀

  • mstjean

    Hall of Fame

    Points: 3310

    You don't indicate what version you're running, but if SQL2005 you could (quick n dirty) do this.. it returns a superset of the columns you used but you can always customize that.

    SELECT

    sp.*,

    DB_NAME(sp.dbid) dbname,

    OBJECT_NAME(st.objectid,sp.dbid) as oname,

    CAST(st.text AS VARCHAR(7000)) as script

    FROM

    sys.sysprocesses sp

    CROSS APPLY

    sys.dm_exec_sql_text (sp.sql_handle) st

    ...BOL mentions sys.sysprocesses is not long for this world so you *really* should use one of these-- depending on what you need:

    sys.dm_exec_connections

    sys.dm_exec_sessions

    sys.dm_exec_requests


    Cursors are useful if you don't know SQL

  • aussierock

    SSC Enthusiast

    Points: 121

    Nice Wave Man! memories of Kirra Point.

  • doc_sewell

    SSC Eights!

    Points: 820

    I have problems with getting it to run. I am getting errors on the single quotes (Unicode probably). Is there an easy way to fix it?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply