Checking to see if tables are locked.

-- Used to check which tables are locked. Also gets the SPID code so you can kill the process and "unlock" the tables.
-- Using WITH (NOLOCK) On the table will give you results
--KILL SPID

IF object_id('tempdb..#locksummary') IS NOT NULL DROP TABLE #locksummary
IF object_id('tempdb..#lock') IS NOT NULL DROP TABLE #lock
CREATE TABLE #lock (    spid INT,    dbid INT,    objId INT,    indId INT,    TYPE CHAR(4),    resource NCHAR(32),    Mode CHAR(8),    STATUS CHAR(6))
INSERT INTO #lock EXEC sp_lock
IF object_id('tempdb..#who') IS NOT NULL DROP TABLE #who
CREATE TABLE #who (     spid INT, ecid INT, STATUS CHAR(30),
            loginame CHAR(128), hostname CHAR(128),
            blk CHAR(5), dbname CHAR(128), cmd CHAR(16)
            --
            , request_id INT --Needed for SQL 2008 onwards
            --
         )
INSERT INTO #who EXEC sp_who
Print '-----------------------------------------'
Print 'Lock Summary for ' + @@servername  + ' (excluding tempdb):'
Print '-----------------------------------------' + CHAR(10)
SELECT     LEFT(loginame, 28) AS loginame,
    LEFT(db_name(dbid),128) AS DB,
    LEFT(object_name(objID),30) AS object,
    MAX(mode) AS [ToLevel],
    COUNT(*) AS [How Many],
    MAX(CASE WHEN mode= 'X' THEN cmd ELSE NULL END) AS [Xclusive LOCK FOR command],
    l.spid, hostname
INTO #LockSummary
FROM #lock l JOIN #who w ON l.spid= w.spid
WHERE dbID != db_id('tempdb') AND l.STATUS='GRANT'
GROUP BY dbID, objID, l.spid, hostname, loginame

SELECT * FROM #LockSummary ORDER BY [ToLevel] DESC, [How Many] DESC, loginame, DB, object

Print '--------'
Print 'Who is blocking:'
Print '--------' + CHAR(10)
SELECT p.spid
,CONVERT(CHAR(12), d.name) db_name
, program_name
, p.loginame
, CONVERT(CHAR(12), hostname) hostname
, cmd
, p.STATUS
, p.blocked
, login_time
, last_batch
, p.spid
FROM      master..sysprocesses p
JOIN      master..sysdatabases d ON p.dbid =  d.dbid
WHERE     EXISTS (  SELECT 1
          FROM      master..sysprocesses p2
          WHERE     p2.blocked = p.spid )

Print '--------'
Print 'Details:'
Print '--------' + CHAR(10)
SELECT     LEFT(loginame, 30) AS loginame,  l.spid,
    LEFT(db_name(dbid),15) AS DB,
    LEFT(object_name(objID),40) AS object,
    mode ,
    blk,
    l.STATUS
FROM #lock l JOIN #who w ON l.spid= w.spid
WHERE dbID != db_id('tempdb') AND blk <>0
ORDER BY mode DESC, blk, loginame, dbID, objID, l.STATUS