DatabaseProcApplicationCreatedLinks
sybsystemprocssp_listsuspect_page  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** Messages for "sp_listsuspect_page"
6     **
7     ** 17260, "Can't run %1! from within a transaction."
8     */
9     
10    create procedure sp_listsuspect_page
11        @dbname varchar(30) = NULL
12    as
13        declare @dbid int, /* dbid of the database */
14            @attrib_id int,
15            @action int,
16            @object_type varchar(2),
17            @msg varchar(1024),
18            @sptlang int,
19            @state int,
20            @old_state int,
21            @susgran int,
22            @class int,
23            @whichone int /* which language? */
24    
25        select @action = 2 /* modify entry */
26        select @class = 10
27        select @attrib_id = 2 /* attribute is SUSPECT PAGES */
28        select @object_type = 'D'
29    
30        if @@trancount = 0
31        begin
32            set chained off
33        end
34    
35        set transaction isolation level 1
36    
37        select @sptlang = @@langid, @whichone = 0
38    
39        if @@langid != 0
40        begin
41            if not exists (
42                    select * from master.dbo.sysmessages where error
43                        between 17050 and 17069
44                        and langid = @@langid)
45                select @sptlang = 0
46        end
47        /*
48        ** Check for input params - dbname, objectid, and options
49        */
50        if @dbname is null
51            select @dbname = db_name()
52    
53        /*
54        **  Verify the database name and get the @dbid 
55        */
56        select @dbid = db_id(@dbname)
57    
58        /*
59        **  If @dbname not found, say so.
60        */
61        if @dbid is NULL
62        begin
63            /*
64            ** 17421, "No such database -- run sp_helpdb to list databases."
65            */
66            exec sp_getmessage 17421, @msg output
67            print @msg
68            return (1)
69        end
70    
71    
72        select "DBName" = convert(varchar(20), @dbname),
73            "Pageid" = convert(varchar(12), int_value),
74            "Object" = convert(varchar(15), object_name(object_info1, @dbid)),
75            "Index" = convert(varchar(4), object_info2),
76            "Access" = substring(char_value, 1, 9)
77        from master.dbo.sysattributes
78        where class = @class AND
79            attribute = @attrib_id AND
80            object_type = @object_type AND
81            object = @dbid
82    
83        return (0)
84    


exec sp_procxmode 'sp_listsuspect_page', 'AnyMode'
go

Grant Execute on sp_listsuspect_page to public
go
RESULT SETS
sp_listsuspect_page_rset_001

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 44
 QTYP 4 Comparison type mismatch smallint = int 44
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 78
 QTYP 4 Comparison type mismatch smallint = int 78
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 79
 QTYP 4 Comparison type mismatch smallint = int 79
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_listsuspect_page  
 MNER 3 No Error Check should check return value of exec 66
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 83
 QISO 3 Set isolation level 35
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
42
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
78
 VNRD 3 Variable is not read @action 25
 VNRD 3 Variable is not read @whichone 37
 VNRD 3 Variable is not read @sptlang 45
 VUNU 3 Variable is not used @state 19
 VUNU 3 Variable is not used @old_state 20
 VUNU 3 Variable is not used @susgran 21
 MRST 2 Result Set Marker 72
 MSUB 2 Subquery Marker 41
 MTR1 2 Metrics: Comments Ratio Comments: 25% 10
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 7dec - 2exi + 2 10
 MTR3 2 Metrics: Query Complexity Complexity: 35 10

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysmessages (1)  
reads table master..sysattributes (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)