if exists ( select name from sysobjects where name = "MS_sqlctrs_locks" )
    drop proc MS_sqlctrs_locks
go

create proc MS_sqlctrs_locks
as
begin

/* Retrieve Total locks */
select distinct count(*) 'Total locks'
from syslocks

select distinct count(*) 'Total Exclusive locks'
from syslocks
where type in (1,3,5,8)

select distinct count(*) 'Total Shared locks'
from syslocks
where type in (2,4,6,9)

select distinct count(*) 'Total Blocking locks'
from syslocks
where type = 256

select distinct count(*) 'Total Demand locks'
from syslocks
where type = 512

/* Retrieve Table locks */
select distinct count(*) 'Table locks - Exclusive'
from syslocks
where type = 1

select distinct count(*) 'Table locks - Shared'
from syslocks
where type = 2

select distinct count(*) 'Table locks - Total'
from syslocks
where type in (1,2)

/* Retrieve Extent locks */
select distinct count(*) 'Extent locks - Exclusive'
from syslocks
where type = 8

select distinct count(*) 'Extent locks - Shared'
from syslocks
where type = 9

select distinct count(*) 'Extent locks - Total'
from syslocks
where type in (8,9)


/* Retrieve Intent locks */
select distinct count(*) 'Intent locks - Exclusive'
from syslocks
where type = 3

select distinct count(*) 'Intent locks - Shared'
from syslocks
where type = 4

select distinct count(*) 'Intent locks - Total'
from syslocks
where type in (3,4)


/* Retrieve Page Locks */
select distinct count(*) 'Page locks - Exclusive'
from syslocks
where type = 5

select distinct count(*) 'Page locks - Shared'
from syslocks
where type = 6

select distinct count(*) 'Page locks - Update'
from syslocks
where type = 7

select distinct count(*) 'Page locks - Total'
from syslocks
where type in (5,6,7)


end

go

grant execute on MS_sqlctrs_locks to public
go



if exists ( select name from sysobjects where name = "MS_sqlctrs_users" )
    drop proc MS_sqlctrs_users
go

create proc MS_sqlctrs_users
as
begin
select distinct sysprocesses.spid, memusage 'Memory (2K Pages)', cpu 'CPU time',
       physical_io,count(syslocks.spid) 'Locks held'
from sysprocesses, syslocks where sysprocesses.spid *= syslocks.spid
group by sysprocesses.spid
end
go


grant execute on MS_sqlctrs_users to public
go
