DatabaseProcApplicationCreatedLinks
sybsystemprocssp_post_xpload  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Messages for "sp_post_xpload"
4     **
5     ** 17260, "Can't run %1! from within a transaction."
6     ** 18600, "%1!: Illegal option. The only legal option is 'force'."
7     */
8     create procedure sp_post_xpload
9         @forceopt varchar(30) = NULL
10    as
11        declare @objid int
12        declare @dbname varchar(30)
13        declare @tablename varchar(255)
14        declare @msg varchar(400)
15        declare @numtable int
16        declare @tablecount int
17        declare @sqltext varchar(400)
18    
19        /*
20        ** dbcc reindex is not allowed in a transaction.
21        */
22        if @@trancount > 0
23        begin
24            /*
25            ** 17260, "Can't run %1! from within a transaction."
26            */
27            raiserror 17260, "sp_post_xpload"
28            return (1)
29        end
30    
31        if @forceopt is not null
32        begin
33            if @forceopt != "force"
34            begin
35                /* 
36                ** 18600, "%1!: Illegal option. 
37                ** The only legal option is 'force'." 
38                */
39                raiserror 18600, sp_post_xpload
40                return (1)
41            end
42        end
43    
44        set chained off
45    
46        set transaction isolation level 1
47    
48        /*
49        ** Check if user has SA role, proc_role will also do auditing
50        ** if required. proc_role will also print error message if required.
51        */
52    
53        if (proc_role("sa_role") = 0)
54            return (1)
55    
56        /*
57        ** Check if this database was loaded from across platform.
58        */
59        set flushmessage on
60        select @dbname = db_name()
61        if not exists (select 1 from master.dbo.sysdatabases
62                where name = @dbname
63                    and (status3 & 524288) != 0)
64        begin
65            select @msg = "Error: Current database "
66                + @dbname
67                + " is not a database that was loaded from a byte-swapped architecture."
68            print @msg
69            return (1)
70        end
71    
72        /*
73        ** Re-partition hash partition tables.
74        */
75    
76        /* Get the number of hash partition tables */
77        select @numtable = count(*)
78        from dbo.sysindexes, dbo.sysobjects
79        where sysobjects.type = 'U'
80            and sysindexes.partitiontype = 2
81            and sysobjects.id = sysindexes.id
82            and (sysindexes.indid = 0 or sysindexes.indid = 1)
83            and sysobjects.sysstat2 & 1024 = 0
84    
85        if (@numtable != 0)
86        begin
87            select @tablecount = 0
88            declare sysobjects_hash_cursor insensitive cursor for
89            select sysobjects.name
90            from dbo.sysindexes(index csysindexes),
91                dbo.sysobjects(index csysobjects)
92            where type = 'U'
93                and sysindexes.partitiontype = 2
94                and sysobjects.id = sysindexes.id
95                and (sysindexes.indid = 0 or sysindexes.indid = 1)
96                and sysobjects.sysstat2 & 1024 = 0
97            open sysobjects_hash_cursor
98            fetch sysobjects_hash_cursor into @tablename
99            while (@@sqlstatus != 2 and @@sqlstatus != 1)
100           begin
101               select @tablecount = @tablecount + 1
102               select @msg = "sp_post_xpload: Repartitioning hash partition table " + @tablename + " ("
103                   + convert(varchar, @tablecount) + "/"
104                   + convert(varchar, @numtable) + ")"
105               print @msg
106               select @sqltext = "reorg rebuild " + @tablename
107               exec (@sqltext)
108               if (@@error != 0)
109               begin
110                   select @msg = "sp_post_xpload: Failed to repartition hash partition table " + @tablename
111                   print @msg
112               end
113               fetch sysobjects_hash_cursor into @tablename
114           end
115       end
116   
117       /*
118       ** Rebuild global clustered index on rr partition tables to regenerate
119       ** partition condition, if the partition key is unichar or univarchar.
120       */
121   
122       /* Get the number of such rr partition tables */
123       select @numtable = count(*)
124       from sysobjects o, sysindexes i1
125       where o.id = i1.id
126           and i1.indid != 0 and i1.indid != 255 and i1.status3 & 1 = 1
127           and o.type = 'U' and o.sysstat2 & 1024 = 0
128           and i1.id in (select i2.id
129               from sysindexes i2, syspartitionkeys p, syscolumns c
130               where i2.id = p.id and p.id = c.id
131                   and i2.indid = p.indid
132                   and i2.indid != 255 and i2.status3 & 1 = 1
133                   and p.colid = c.colid
134                   and c.type in (135, 155))
135       if (@numtable != 0)
136       begin
137           select @tablecount = 0
138           declare sysobjects_rrind_cursor cursor for
139           select o.name
140           from sysobjects o(index csysobjects),
141               sysindexes i1(index csysindexes)
142           where o.id = i1.id
143               and i1.indid != 0 and i1.indid != 255 and i1.status3 & 1 = 1
144               and o.type = 'U' and o.sysstat2 & 1024 = 0
145               and i1.id in (select i2.id
146                   from sysindexes i2(index csysindexes),
147                       syspartitionkeys p(index csyspartitionkeys),
148                       syscolumns c(index csyscolumns)
149                   where i2.id = p.id and p.id = c.id
150                       and i2.indid = p.indid
151                       and i2.indid != 255 and i2.status3 & 1 = 1
152                       and p.colid = c.colid
153                       and c.type in (135, 155))
154           open sysobjects_rrind_cursor
155           fetch sysobjects_rrind_cursor into @tablename
156           while (@@sqlstatus != 2 and @@sqlstatus != 1)
157           begin
158               select @tablecount = @tablecount + 1
159               select @msg = "sp_post_xpload: Rebuilding clustered index on table " + @tablename + " ("
160                   + convert(varchar, @tablecount) + "/"
161                   + convert(varchar, @numtable) + ")"
162               print @msg
163               /* Force rebuild of indexes including APL clustered index */
164               dbcc reindex(@tablename, 24)
165               fetch sysobjects_rrind_cursor into @tablename
166           end
167       end
168   
169       /*
170       ** Rebuild indexes on user tables.
171       */
172   
173       select @numtable = count(*)
174       from dbo.sysobjects
175       where type = 'U'
176   
177       select @tablecount = 0
178   
179       declare sysobjects_cursor cursor for
180       select id, name
181       from dbo.sysobjects(index ncsysobjects)
182       where type = 'U'
183   
184       open sysobjects_cursor
185   
186       fetch sysobjects_cursor into @objid, @tablename
187   
188       while (@@sqlstatus != 2 and @@sqlstatus != 1)
189       begin
190           select @tablecount = @tablecount + 1
191           select @msg = "sp_post_xpload: Processing table " + @tablename + " ("
192               + convert(varchar, @tablecount) + "/"
193               + convert(varchar, @numtable) + ")"
194   
195           print @msg
196           if exists (select id
197                   from dbo.sysindexes
198                   where id = @objid
199                       and indid > 1 and indid != 255)
200           begin
201               if @forceopt = "force"
202                   /* Force rebuild index except APL clustered index.*/
203                   dbcc reindex(@objid, 30)
204               else
205                   /*	
206                   ** We don't need to check a APL table which has
207                   ** only clustered index.
208                   ** Need to check and rebuild non-clustered index on 
209                   ** APL or clusterd/non-cluster index on DOL.
210                   */
211                   dbcc reindex(@objid, 14)
212           end
213   
214           fetch sysobjects_cursor into @objid, @tablename
215       end
216   
217       close sysobjects_cursor
218   
219       deallocate cursor sysobjects_cursor
220   
221       checkpoint
222   
223       return (0)
224
DEFECTS
 QJWI 5 Join or Sarg Without Index 128
 QJWI 5 Join or Sarg Without Index 145
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}		 80
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}		 93
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}		 126
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}		 132
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}		 143
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}		 151
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 80
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 82
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 93
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 95
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 126
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 132
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 143
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 151
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 199
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysobjects_cursor 180
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 8
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..syspartitionkeys  
 MUCO 3 Useless Code Useless Brackets 28
 MUCO 3 Useless Code Useless Brackets 40
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 223
 QFID 3 Force index sybsystemprocs..sysindexes 90
 QFID 3 Force index sybsystemprocs..sysobjects 91
 QFID 3 Force index sybsystemprocs..sysobjects 140
 QFID 3 Force index sybsystemprocs..sysindexes 141
 QFID 3 Force index sybsystemprocs..sysindexes 146
 QFID 3 Force index sybsystemprocs..syspartitionkeys 147
 QFID 3 Force index sybsystemprocs..syscolumns 148
 QFID 3 Force index sybsystemprocs..sysobjects 181
 QISO 3 Set isolation level 46
 QNAJ 3 Not using ANSI Inner Join 78
 QNAJ 3 Not using ANSI Inner Join 90
 QNAJ 3 Not using ANSI Inner Join 124
 QNAJ 3 Not using ANSI Inner Join 129
 QNAJ 3 Not using ANSI Inner Join 140
 QNAJ 3 Not using ANSI Inner Join 146
 QNUA 3 Should use Alias: Table sybsystemprocs..sysindexes 78
 QNUA 3 Should use Alias: Table sybsystemprocs..sysobjects 78
 QNUA 3 Should use Alias: Table sybsystemprocs..sysindexes 90
 QNUA 3 Should use Alias: Table sybsystemprocs..sysobjects 91
 QNUA 3 Should use Alias: Column type should use alias sysobjects 92
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
Uncovered: [number]		 130
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syspartitionkeys.csyspartitionkeys unique clustered
(id, indid, colid)
Intersection: {colid, id}
Uncovered: [indid]		 130
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
Uncovered: [number]		 149
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syspartitionkeys.csyspartitionkeys unique clustered
(id, indid, colid)
Intersection: {colid, id}
Uncovered: [indid]		 149
 CRDO 2 Read Only Cursor Marker (has subqueries) 139
 CUPD 2 Updatable Cursor Marker (updatable by default) 89
 CUPD 2 Updatable Cursor Marker (updatable by default) 180
 MDYS 2 Dynamic SQL Marker 107
 MSUB 2 Subquery Marker 61
 MSUB 2 Subquery Marker 128
 MSUB 2 Subquery Marker 145
 MSUB 2 Subquery Marker 196
 MTR1 2 Metrics: Comments Ratio Comments: 19% 8
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 51 = 53dec - 4exi + 2 8
 MTR3 2 Metrics: Query Complexity Complexity: 152 8
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 77
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 89
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, i=sybsystemprocs..sysindexes, p=sybsystemprocs..syspartitionkeys} 0 128
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, i=sybsystemprocs..sysindexes, p=sybsystemprocs..syspartitionkeys} 0 145

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..syscolumns  
reads table master..sysdatabases (1)  
reads table sybsystemprocs..syspartitionkeys  
reads table sybsystemprocs..sysindexes