usp_SQL_In_sp_who2

Question

usp_SQL_In_sp_who2

Brandon Forest

SSCommitted

Points: 1977
More actions
March 27, 2009 at 9:19 pm

#219479

Comments posted to this topic are about the item usp_SQL_In_sp_who2

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

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

lbabs SSC Journeyman Points: 80 More actions · Answer 1

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: 2513 More actions · Answer 2

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

mstjean Hall of Fame Points: 3310 More actions · Answer 3

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: 151 More actions · Answer 4

aussierock

SSC Enthusiast

Points: 151

April 24, 2009 at 3:27 pm

#983852

Nice Wave Man! memories of Kirra Point.

doc_sewell SSC Eights! Points: 830 More actions · Answer 5

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?