| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_listsuspect_db | | 31 Aug 14 | Defects 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) |