usp_SQL_In_sp

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Brandon Forest » usp_SQL_In_sp_who2

usp_SQL_In_sp_who2

Rate Topic

Display Mode

Topic Options

Author

Message

Brandon Forest

Posted Friday, March 27, 2009 9:19 PM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:29 AM
Points: 60, Visits: 303

Comments posted to this topic are about the item usp_SQL_In_sp_who2

Post #685491

lbabs

Posted Friday, April 24, 2009 7:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 6:00 AM
Points: 4, Visits: 64

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.

Post #703913

Alan Vogan

Posted Friday, April 24, 2009 8:46 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 10:44 AM
Points: 1,079, Visits: 513

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

Post #704023

mstjean

Posted Friday, April 24, 2009 3:07 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:15 PM
Points: 423, Visits: 1,948

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

Post #704323

aussierock

Posted Friday, April 24, 2009 3:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 11:36 AM
Points: 5, Visits: 104

Nice Wave Man! memories of Kirra Point.

Post #704326

doc_sewell

Posted Monday, June 14, 2010 8:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 04, 2012 8:18 AM
Points: 155, Visits: 123

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?

Post #936868

« Prev Topic | Next Topic »

Permissions