DatabaseProcApplicationCreatedLinks
sybsystemprocssp_indsuspect  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages from sysmessages
6     **
7     ** 17860, "Suspect indexes in database %1!:"
8     ** 17861, "There are no suspect indexes in database %1!."
9     ** 17862, "Table must be in the 'current' database."
10    ** 17863, "There is no table named %1! in the 'current' database."
11    ** 17864, "Suspect indexes on table %1!:"
12    ** 17865, "There are no suspect indexes on table %1!."
13    */
14    
15    create procedure sp_indsuspect
16        @tab_name varchar(767) = NULL /* if NULL, check all tables in db */
17    AS
18    
19        declare @msg varchar(1024) /* used for message to user */
20        declare @msg2 varchar(30) /* used for table/db name */
21        declare @ind_suspect smallint /* value of IND_SUSPECT bit */
22    
23    
24        if @@trancount = 0
25        begin
26            set chained off
27        end
28    
29        set transaction isolation level 1
30    
31        set nocount on
32    
33        /* 
34        ** Specify the bit mask as -32768, not in hex notation, for portability
35        ** to byte-swapped platforms.  This could break on a 1's complement 
36        ** platform, but there isn't really a better solution.  SQL handles 
37        ** hex numbers as binary data, not as numeric values as in C.
38        */
39    
40        select @ind_suspect = - 32768
41    
42        if (@tab_name is null)
43        begin
44            /*
45            ** No table name specified, sp search for any
46            ** suspected indexes in the entire database.
47            */
48            if exists (select * from sysindexes i
49                    where (i.status & @ind_suspect) != 0)
50            begin
51                /* 17860, "Suspect indexes in database %1!:" */
52                exec sp_getmessage 17860, @msg output
53                select @msg2 = db_name()
54                print @msg, @msg2
55    
56                select
57                    "Own.Tab.Ind (Obj_ID, Ind_ID)" =
58                    u.name + "." + o.name + '.' + i.name +
59                    ' (' + convert(varchar, o.id) + ', ' +
60                    convert(varchar, i.indid) + ')'
61                from
62                    sysobjects o,
63                    sysindexes i,
64                    sysusers u
65                where
66                    o.id = i.id
67                    and o.uid = u.uid
68                    and (i.status & @ind_suspect) != 0
69    
70                exec sp_autoformat @fulltabname = #temp1,
71                    @selectlist = "'Own.Tab.Ind (Obj_ID, Ind_ID)' = name"
72            end
73            else
74            begin
75                /* 17861, "There are no suspect indexes in database %1!." */
76                exec sp_getmessage 17861, @msg output
77                select @msg2 = db_name()
78                print @msg, @msg2
79            end
80        end
81        else
82        begin
83            /*
84            ** User has specified a table.
85            ** Check to see that the table name is local to the current database.
86            */
87            if (@tab_name like '%.%.%')
88            begin
89                /* 17862, "Table must be in the 'current' database." */
90                raiserror 17862
91                return (1)
92            end
93    
94            /*
95            ** Now, see if the table actually exists.
96            */
97            if (object_id(@tab_name) is null)
98            begin
99                /* 17863, "There is no table named %1! in the 'current' database." */
100               raiserror 17863, @tab_name
101               return (2)
102           end
103   
104           /*
105           ** Now, check for any suspect indexes on this table.
106           */
107           if exists (select * from sysindexes i
108                   where i.id = object_id(@tab_name)
109                       and (i.status & @ind_suspect) != 0)
110           begin
111               /* 17864, "Suspect indexes on table %1!:" */
112               exec sp_getmessage 17864, @msg output
113               print @msg, @tab_name
114   
115               select
116                   "Own.Tab.Ind (Obj_ID, Ind_ID)" =
117                   u.name + "." + o.name + '.' + i.name +
118                   ' (' + convert(varchar, o.id) + ', ' +
119                   convert(varchar, i.indid) + ')'
120               from
121                   sysobjects o,
122                   sysindexes i,
123                   sysusers u
124               where
125                   o.id = i.id
126                   and o.uid = u.uid
127                   and o.id = object_id(@tab_name)
128                   and (i.status & @ind_suspect) != 0
129           end
130           else
131           begin
132               /* 17865, "There are no suspect indexes on table %1!." */
133               exec sp_getmessage 17865, @msg output
134               print @msg, @tab_name
135           end
136       end
137       return (0)
138   


exec sp_procxmode 'sp_indsuspect', 'AnyMode'
go

Grant Execute on sp_indsuspect to public
go
RESULT SETS
sp_indsuspect_rset_002
sp_indsuspect_rset_001

DEFECTS
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 70
 MGTP 3 Grant to public sybsystemprocs..sp_indsuspect  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check return value of exec 52
 MNER 3 No Error Check should check return value of exec 70
 MNER 3 No Error Check should check return value of exec 76
 MNER 3 No Error Check should check return value of exec 112
 MNER 3 No Error Check should check return value of exec 133
 MUCO 3 Useless Code Useless Brackets 42
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 137
 QCRS 3 Conditional Result Set 56
 QCRS 3 Conditional Result Set 115
 QISO 3 Set isolation level 29
 QNAJ 3 Not using ANSI Inner Join 61
 QNAJ 3 Not using ANSI Inner Join 120
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
108
 MRST 2 Result Set Marker 56
 MRST 2 Result Set Marker 115
 MSUB 2 Subquery Marker 48
 MSUB 2 Subquery Marker 107
 MTR1 2 Metrics: Comments Ratio Comments: 39% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 7dec - 3exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 56 15
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects, u=sybsystemprocs..sysusers} 0 56
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects, u=sybsystemprocs..sysusers} 0 115

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysindexes  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
reads table sybsystemprocs..sysusers  
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_autoformat  
   reads table master..systypes (1)  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..syscolumns (1)  
   reads table tempdb..systypes (1)  
   calls proc sybsystemprocs..sp_namecrack