DatabaseProcApplicationCreatedLinks
sybsystemprocssp_familylock  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/lock */
4     /*
5     ** The output of this stored proc is almost identical to the output of
6     ** sp_lock. It differs in that the rows displayed here are a subset of
7     ** the output rows of sp_lock having the parent spid represented by the
8     ** parameters. If sp_familylock is invoked with no parameter it will 
9     ** report information on all processes (belonging to families) that 
10    ** currently hold locks.
11    **
12    ** Messages for "sp_familylock"
13    **
14    ** 18052, "The class column will display the cursor name for locks associated
15    **         with a cursor for the current user and the cursor id for other 
16    **         users."
17    */
18    
19    
20    create procedure sp_familylock
21        @fpid1 int = NULL, /* server family id to check for locks */
22        @fpid2 int = NULL /* other family id to check for locks */
23    as
24    
25        declare @length int
26        declare @msg varchar(1024)
27    
28    
29        if @@trancount = 0
30        begin
31            set chained off
32        end
33    
34        set transaction isolation level 1
35    
36        /*  Print warning message about cursor lock info:
37        **  18052, "The class column will display the cursor name for locks associated
38        **          with a cursor for the current user and the cursor id for other 
39        **          users."
40        */
41        exec sp_getmessage 18052, @msg out
42        print @msg
43    
44        /* RESOLVE: ensure that fpid1 and fpid2 are not NULL. */
45        /*
46        **  Show the locks for both parameters.
47        */
48        if @fpid1 is not NULL
49        begin
50            select @length = max(datalength(db_name(dbid)))
51            from master..syslocks
52            where fid in (@fpid1, @fpid2)
53    
54            /* Print the familyid and context type of the lock and order the output
55            ** by fid, spid.
56            */
57            if (@length > 15)
58            begin
59    
60                select fid, spid, loid, locktype = v1.name, table_id = l.id, page,
61                    dbname = db_name(dbid), class, context = v2.name
62                from master..syslocks l, master..spt_values v1, master..spt_values v2
63                where l.type = v1.number
64                    and v1.type = "L"
65                    and (l.context + 2048) = v2.number
66                    and v2.type = "L2"
67                    and fid in (@fpid1, @fpid2)
68                    and fid in (select fid from master..sysprocesses
69                        where fid != 0)
70                order by fid, spid, loid, dbname, table_id, locktype, page
71            end
72            else
73                select fid, spid, loid, locktype = v1.name, table_id = l.id, page,
74                    dbname = convert(char(15), db_name(dbid)), class, context = v2.name
75                from master..syslocks l, master..spt_values v1, master..spt_values v2
76                where l.type = v1.number
77                    and v1.type = "L"
78                    and (l.context + 2048) = v2.number
79                    and v2.type = "L2"
80                    and fid in (@fpid1, @fpid2)
81                    and fid in (select fid from master..sysprocesses
82                        where fid != 0)
83                order by fid, spid, loid, dbname, table_id, locktype, page
84        end
85    
86        /*
87        **  No parameters, so show all the locks belonging to family tasks.
88        */
89        else
90        begin
91            select @length = max(datalength(db_name(dbid)))
92            from master..syslocks
93    
94            if (@length > 15)
95                select fid, l.spid, loid, locktype = v1.name, table_id = l.id, page,
96                    dbname = db_name(l.dbid), class, context = v2.name
97                from master..syslocks l, master..spt_values v1, master..spt_values v2
98                where l.type = v1.number
99                    and v1.type = "L"
100                   and (l.context + 2048) = v2.number
101                   and v2.type = "L2"
102                   and fid in (select distinct fid from master..sysprocesses
103                       where fid != 0)
104               order by fid, spid, loid, dbname, table_id, locktype, page
105           else
106               select fid, l.spid, loid, locktype = v1.name, table_id = l.id, page,
107                   dbname = convert(char(15), db_name(l.dbid)), class, context = v2.name
108               from master..syslocks l, master..spt_values v1, master..spt_values v2
109               where l.type = v1.number
110                   and v1.type = "L"
111                   and (l.context + 2048) = v2.number
112                   and v2.type = "L2"
113                   and fid in (select distinct fid from master..sysprocesses
114                       where fid != 0)
115               order by fid, spid, loid, dbname, table_id, locktype, page
116       end
117   
118       return (0)
119   


exec sp_procxmode 'sp_familylock', 'AnyMode'
go

Grant Execute on sp_familylock to public
go
RESULT SETS
sp_familylock_rset_004
sp_familylock_rset_003
sp_familylock_rset_002
sp_familylock_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 68
 QJWI 5 Join or Sarg Without Index 81
 QJWI 5 Join or Sarg Without Index 102
 QJWI 5 Join or Sarg Without Index 113
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 63
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 69
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 76
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 82
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 98
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 103
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 109
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 114
 TNOI 4 Table with no index master..syslocks master..syslocks
 TNOI 4 Table with no index master..sysprocesses master..sysprocesses
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..syslocks  
 MGTP 3 Grant to public master..sysprocesses  
 MGTP 3 Grant to public sybsystemprocs..sp_familylock  
 MNER 3 No Error Check should check return value of exec 41
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 118
 QCRS 3 Conditional Result Set 60
 QCRS 3 Conditional Result Set 73
 QCRS 3 Conditional Result Set 95
 QCRS 3 Conditional Result Set 106
 QGWO 3 Group by/Distinct/Union without order by 102
 QGWO 3 Group by/Distinct/Union without order by 113
 QISO 3 Set isolation level 34
 QNAJ 3 Not using ANSI Inner Join 62
 QNAJ 3 Not using ANSI Inner Join 75
 QNAJ 3 Not using ANSI Inner Join 97
 QNAJ 3 Not using ANSI Inner Join 108
 QNUA 3 Should use Alias: Column fid should use alias l 60
 QNUA 3 Should use Alias: Column loid should use alias l 60
 QNUA 3 Should use Alias: Column page should use alias l 60
 QNUA 3 Should use Alias: Column spid should use alias l 60
 QNUA 3 Should use Alias: Column class should use alias l 61
 QNUA 3 Should use Alias: Column dbid should use alias l 61
 QNUA 3 Should use Alias: Column fid should use alias l 67
 QNUA 3 Should use Alias: Column fid should use alias l 68
 QNUA 3 Should use Alias: Table master..sysprocesses 68
 QNUA 3 Should use Alias: Column fid should use alias l 73
 QNUA 3 Should use Alias: Column loid should use alias l 73
 QNUA 3 Should use Alias: Column page should use alias l 73
 QNUA 3 Should use Alias: Column spid should use alias l 73
 QNUA 3 Should use Alias: Column class should use alias l 74
 QNUA 3 Should use Alias: Column dbid should use alias l 74
 QNUA 3 Should use Alias: Column fid should use alias l 80
 QNUA 3 Should use Alias: Column fid should use alias l 81
 QNUA 3 Should use Alias: Table master..sysprocesses 81
 QNUA 3 Should use Alias: Column fid should use alias l 95
 QNUA 3 Should use Alias: Column loid should use alias l 95
 QNUA 3 Should use Alias: Column page should use alias l 95
 QNUA 3 Should use Alias: Column class should use alias l 96
 QNUA 3 Should use Alias: Column fid should use alias l 102
 QNUA 3 Should use Alias: Table master..sysprocesses 102
 QNUA 3 Should use Alias: Column fid should use alias l 106
 QNUA 3 Should use Alias: Column loid should use alias l 106
 QNUA 3 Should use Alias: Column page should use alias l 106
 QNUA 3 Should use Alias: Column class should use alias l 107
 QNUA 3 Should use Alias: Column fid should use alias l 113
 QNUA 3 Should use Alias: Table master..sysprocesses 113
 MRST 2 Result Set Marker 60
 MRST 2 Result Set Marker 73
 MRST 2 Result Set Marker 95
 MRST 2 Result Set Marker 106
 MSUB 2 Subquery Marker 68
 MSUB 2 Subquery Marker 81
 MSUB 2 Subquery Marker 102
 MSUB 2 Subquery Marker 113
 MTR1 2 Metrics: Comments Ratio Comments: 32% 20
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 4dec - 1exi + 2 20
 MTR3 2 Metrics: Query Complexity Complexity: 67 20

DEPENDENCIES
PROCS AND TABLES USED
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
reads table master..syslocks (1)  
reads table master..sysprocesses (1)