Monday, May 13, 2013

Examine sp_who2 with LinqPad

This script gets for you just the list of blockers and the list of blockees separately

Prerequisite: Make sure you check Include System Views and SPs in the SQL connection dialog.

var v= sys.sp_who2().Tables[0].AsEnumerable();//.Dump("SP_WHO2");

var blockers = v
 .Where(x=>x.Field("BlkBy") != "  .")
 .Select (x => x.Field("BlkBy")).Distinct();//.Dump("Blockers");  
var blockerDetails = v
 .Where (x =>blockers.Contains(x.Field("SPID")) ).Dump("Blockers");
var blockees = v.Where(x=> x.Field("BlkBy") != "  .").Dump("Blockees");