DatabaseProcApplicationCreatedLinks
sybsystemprocssp_listsuspect_db  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     
5     /*
6     ** 17260, "Can't run %1! from within a transaction." 
7     ** 17421, "No such database -- run sp_helpdb to list databases."
8     ** 17422, "The 'master' database's options can not be changed."
9     ** 17428, "You must be in the 'master' database in order to change
10    **	database options."                                
11    ** 18530, "The database '%1!' has %2! suspect pages belonging to %3! objects"
12    */
13    
14    create procedure sp_listsuspect_db
15    as
16        declare @dbid int, /* dbid of the database */
17            @class int,
18            @attrib_id int,
19            @action int,
20            @object_type varchar(2),
21            @msg varchar(1024),
22            @sptlang int,
23            @state int,
24            @old_state int,
25            @susgran int,
26            @numpgs int,
27            @numobjs int,
28            @dbname varchar(40),
29            @pgid int,
30            @whichone int /* which language? */
31    
32        select @class = 10 /* for suspect granularity */
33        select @action = 2 /* modify entry */
34        select @attrib_id = 1 /* attribute is SUSPECT PAGES */
35        select @object_type = 'D'
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        declare suspect_db cursor for
49        select object from master.dbo.sysattributes
50        where class = @class AND
51            attribute = 0 AND
52            object_type = @object_type
53        open suspect_db
54        fetch suspect_db into @dbid
55    
56        while (@@sqlstatus = 0)
57        begin
58            select @numpgs = count(*) from master.dbo.sysattributes
59            where class = @class AND
60                attribute = 2 AND
61                object_type = @object_type AND
62                object = @dbid
63    
64            select @numobjs = count(*) from master.dbo.sysattributes
65            where class = @class AND
66                attribute = 3 AND
67                object_type = @object_type AND
68                object = @dbid
69    
70            /*
71            ** 18530, "The database '%1!' has %2! suspect pages belonging to %3! objects"
72            */
73            if (@numpgs > 0)
74            begin
75                select @dbname = db_name(@dbid)
76                exec sp_getmessage 18530, @msg output
77                print @msg, @dbname, @numpgs, @numobjs
78            end
79            fetch suspect_db into @dbid
80        end
81        close suspect_db
82        deallocate cursor suspect_db
83    
84        return (0)
85    


exec sp_procxmode 'sp_listsuspect_db', 'AnyMode'
go

Grant Execute on sp_listsuspect_db to public
go
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 50
 QTYP 4 Comparison type mismatch smallint = int 50
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 51
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 59
 QTYP 4 Comparison type mismatch smallint = int 59
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 60
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 65
 QTYP 4 Comparison type mismatch smallint = int 65
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 66
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause suspect_db 49
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_listsuspect_db  
 MNER 3 No Error Check should check return value of exec 76
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 84
 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: {class, object_type, attribute}		 50
 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}		 59
 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}		 65
 VNRD 3 Variable is not read @action 33
 VNRD 3 Variable is not read @attrib_id 34
 VNRD 3 Variable is not read @whichone 37
 VNRD 3 Variable is not read @sptlang 45
 VUNU 3 Variable is not used @state 23
 VUNU 3 Variable is not used @old_state 24
 VUNU 3 Variable is not used @susgran 25
 VUNU 3 Variable is not used @pgid 29
 CUPD 2 Updatable Cursor Marker (updatable by default) 49
 MSUB 2 Subquery Marker 41
 MTR1 2 Metrics: Comments Ratio Comments: 27% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 14dec - 1exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 45 14

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