DatabaseProcApplicationCreatedLinks
sybsystemprocssp_tab_suspectptn  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages from sysmessages
6     **
7     ** 17862, "Table must be in the 'current' database."
8     ** 17863, "There is no table named %1! in the 'current' database."
9     ** 19649, "Suspect partitioning in database %1!:"
10    ** 19650, "There are no suspect partitioning in database %1!."
11    ** 19651, "Suspect partitioning on table %1!:"
12    ** 19652, "There are no suspect partitioning on table %1!."
13    */
14    
15    /*
16    **      Procedure sp_tab_suspectptn
17    **
18    **	Range-partition table on char-based partition keys can become
19    **	suspect after sort order change. Hash-partitioned table can
20    **	become suspect after cross-platform dump load. This procedure 
21    **	provides the administration interface for listing the table(s)
22    **	with suspect partitioning. The IND3_PTN_SUSPECT is set at table
23    **	level. Either all partitions in a table are suspect, or no 
24    **	partition is suspect.
25    **      
26    */
27    
28    create procedure sp_tab_suspectptn
29        @tab_name varchar(767) = NULL /* if NULL, check all tables in db */
30    AS
31    
32        declare @msg varchar(1024) /* used for message to user */
33        declare @msg2 varchar(256) /* used for table/db name */
34        declare @ptn_suspect smallint /* value of IND3_PTN_SUSPECT bit */
35    
36    
37        if @@trancount = 0
38        begin
39            set chained off
40        end
41    
42        set transaction isolation level 1
43    
44        set nocount on
45    
46        /* 
47        ** Specify the bit mask as 16, not in hex notation, for portability
48        ** to byte-swapped platforms.  This could break on a 1's complement 
49        ** platform, but there isn't really a better solution.  SQL handles 
50        ** hex numbers as binary data, not as numeric values as in C.
51        */
52    
53        select @ptn_suspect = 16
54    
55        if (@tab_name is null)
56        begin
57            /*
58            ** No table name specified, sp search for any
59            ** suspected partitions in the entire database.
60            */
61            if exists (select * from sysindexes i
62                    where (i.status3 & @ptn_suspect) != 0)
63            begin
64                /* 19649, "Suspect partitioning in database %1!:" */
65                exec sp_getmessage 19649, @msg output
66                select @msg2 = db_name()
67                print @msg, @msg2
68    
69                select
70                    "Partition type, Own.Tab.Ind (Obj_ID, Ind_ID)" =
71                    v.name + "," + u.name + "." + o.name + '.' + i.name +
72                    '.' + ' (' + convert(varchar, o.id) +
73                    ', ' + convert(varchar, i.indid) + ')'
74                from
75                    sysobjects o,
76                    sysindexes i,
77                    sysusers u,
78                    master.dbo.spt_values v
79                where
80                    o.id = i.id
81                    and o.uid = u.uid
82                    and i.partitiontype = v.number
83                    and v.type = 'PN'
84                    and (i.status3 & @ptn_suspect) != 0
85            end
86            else
87            begin
88                /* 19650, "There are no suspect partitioning in database %1!." */
89                exec sp_getmessage 19650, @msg output
90                select @msg2 = db_name()
91                print @msg, @msg2
92            end
93        end
94        else
95        begin
96            /*
97            ** User has specified a table.
98            ** Check to see that the table name is local to the current database.
99            */
100           if (@tab_name like '%.%.%')
101           begin
102               /* 17862, "Table must be in the 'current' database." */
103               raiserror 17862
104               return (1)
105           end
106   
107           /*
108           ** Now, see if the table actually exists.
109           */
110           if (object_id(@tab_name) is null)
111           begin
112               /* 17863, "There is no table named %1! in the 'current' database." */
113               raiserror 17863, @tab_name
114               return (2)
115           end
116   
117           /*
118           ** Now, check for any suspect partitioning on this table.
119           */
120           if exists (select * from sysindexes i
121                   where i.id = object_id(@tab_name)
122                       and (i.status3 & @ptn_suspect) != 0)
123           begin
124               /* 19651, "Suspect partitioning on table %1!:" */
125               exec sp_getmessage 19651, @msg output
126               print @msg, @tab_name
127   
128               select
129                   "Partition Type, Own.Tab.Ind.Ptn (Obj_ID, Ind_ID, Ptn_ID)" =
130                   v.name + ", " + u.name + "." + o.name + '.' + i.name +
131                   '.' + p.name + ' (' + convert(varchar, o.id) +
132                   ', ' + convert(varchar, i.indid) +
133                   ', ' + convert(varchar, p.partitionid) + ')'
134               from
135                   sysobjects o,
136                   sysindexes i,
137                   sysusers u,
138                   syspartitions p,
139                   master.dbo.spt_values v
140               where
141                   o.id = i.id
142                   and o.uid = u.uid
143                   and i.id = p.id
144                   and i.indid = p.indid
145                   and i.partitiontype = v.number
146                   and v.type = 'PN'
147                   and o.id = object_id(@tab_name)
148                   and (i.status3 & @ptn_suspect) != 0
149           end
150           else
151           begin
152               /* 19652, "There are no suspect partitioning on table %1!." */
153               exec sp_getmessage 19652, @msg output
154               print @msg, @tab_name
155           end
156       end
157       return (0)
158   


exec sp_procxmode 'sp_tab_suspectptn', 'AnyMode'
go

Grant Execute on sp_tab_suspectptn to public
go
RESULT SETS
sp_tab_suspectptn_rset_002
sp_tab_suspectptn_rset_001

DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 82
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 145
 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 sybsystemprocs..sp_tab_suspectptn  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..syspartitions  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check return value of exec 65
 MNER 3 No Error Check should check return value of exec 89
 MNER 3 No Error Check should check return value of exec 125
 MNER 3 No Error Check should check return value of exec 153
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 157
 QCRS 3 Conditional Result Set 69
 QCRS 3 Conditional Result Set 128
 QISO 3 Set isolation level 42
 QNAJ 3 Not using ANSI Inner Join 74
 QNAJ 3 Not using ANSI Inner Join 134
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}		 121
 MRST 2 Result Set Marker 69
 MRST 2 Result Set Marker 128
 MSUB 2 Subquery Marker 61
 MSUB 2 Subquery Marker 120
 MTR1 2 Metrics: Comments Ratio Comments: 44% 28
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 7dec - 3exi + 2 28
 MTR3 2 Metrics: Query Complexity Complexity: 64 28
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects, sv=master..spt_values, u=sybsystemprocs..sysusers} 0 69
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects, p=sybsystemprocs..syspartitions, sv=master..spt_values, u=sybsystemprocs..sysusers} 0 128

DEPENDENCIES
PROCS AND TABLES USED
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 sybsystemprocs..syspartitions  
reads table master..spt_values (1)  
reads table sybsystemprocs..sysusers  
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysindexes