DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropsegment  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "@(#) generic/sproc/src/%M% %I% %G%" */
3     
4     /*	4.8	1.1	06/14/90	sproc/src/defaultlanguage */
5     
6     /*
7     ** Messages for "sp_dropsegment"        17520
8     **
9     ** 17260, "Can't run %1! from within a transaction."
10    ** 17281, "The specified device is not used by the database."
11    ** 17520, "There is no such segment as '%1!'."
12    ** 17521, "Can't drop the '%1!'segment completely."
13    ** 17522, "The segment '%1!' is being used."
14    ** 17523, "Segment '%1!' does not reference device '%2!'."
15    ** 17524, "There is only one device mapping for the segment '%1!' -- use sp_dropsegment with no device argument."
16    ** 17525, "Segment dropped."
17    ** 17526, "Segment reference to device dropped."
18    ** 17527, "WARNING: There are no longer any segments referencing device '%1!'.  This device will no longer be used for space allocation."
19    ** 17528, "WARNING: There are no longer any segments referencing devices '%1!'.  These devices will no longer be used for space allocation."
20    ** 17590, "The specified database does not exist." 
21    ** 17715, "The last-chance threshold for database %1! is now %2! pages."
22    ** 17716, "Could not update the last-chance threshold for database %1!"
23    ** 17288, "You must execute this procedure from the database 
24    ** 	  in which you wish to %1! a segment.  Please execute 
25    **	  'use %2!' and try again."
26    ** 17289, "Set your curwrite to the hurdle of current database."
27    */
28    
29    create or replace procedure sp_dropsegment
30        @segname varchar(255), /* segment name */
31        @dbname varchar(255), /* database name */
32        @device varchar(255) = null /* device name */
33    as
34    
35        declare @dbuid int /* id of the owner of the database */
36        declare @dbid smallint /* id of the database */
37        declare @segbit int /* this is the bit to turn on in sysusages */
38        declare @segnum int /* this is the segment number*/
39        declare @lct int /* log's last-chance threshold setting */
40        declare @msg varchar(1024)
41        declare @procval int
42        declare @bitdesc varchar(30) /* bit description for the db */
43        declare @tempdb_mask int /* All database status bit for a tempdb */
44        declare @isatempdb int /* Is this a temp. db ? */
45        declare @isamaster int
46        declare @status int
47        declare @status1 int
48        declare @nullarg varchar(1)
49        declare @gp_enabled int
50    
51        select @procval = 0
52        select @status1 = 1
53    
54        /*
55        **  If we're in a transaction, disallow this since it might make recovery
56        **  impossible.
57        */
58        if @@trancount > 0
59        begin
60            /*
61            ** 17260, "Can't run %1! from within a transaction."
62            */
63            raiserror 17260, "sp_dropsegment"
64            return (1)
65        end
66        else
67        begin
68            set chained off
69        end
70    
71        set transaction isolation level 1
72    
73        /*
74        **  Only the Database Owner (DBO) or
75        **  Accounts with SA role can execute it.
76        **  Call proc_role() with the required SA role.
77        */
78        execute @status = sp_aux_checkroleperm "dbo",
79            "manage database", @dbname, @gp_enabled output
80    
81        if (@status != 0)
82        begin
83            if (@gp_enabled = 0)
84            begin
85                select @nullarg = NULL
86                execute @status1 = sp_aux_checkroleperm "sa_role",
87                    @nullarg, @dbname, @gp_enabled output
88                if (@status1 > 0)
89                begin
90                    /* 
91                    ** user_id() is not DBO hence user does not 
92                    ** have SA role audit this as a failed sa 
93                    ** command execution.
94                    */
95                    select @status1 = proc_role("sa_role")
96                    return (1)
97                end
98            end
99            else
100           begin
101               select @status1 =
102                   proc_auditperm("manage database",
103                       @status, @dbname)
104               return 1
105           end
106       end
107   
108       /* If user has sa role or "manage database" permission audit this as a
109       ** successful sa command execution.
110       */
111       if (@gp_enabled = 0)
112       begin
113           if (@status1 = 0)
114               select @status1 = proc_role("sa_role")
115       end
116       else
117       begin
118           select @status1 = proc_auditperm("manage database", @status,
119                   @dbname)
120       end
121   
122       /*
123       **  There are two drop cases:
124       **	1) dropping the segment
125       **	2) dropping a segment reference to a device
126       **  The sproc does things differently depending on which we're doing.
127       */
128   
129       /*
130       **  Make ure the database exists
131       */
132       if not exists (select * from master.dbo.sysdatabases
133               where name = @dbname)
134       begin
135           /* 17590, "The specified database does not exist." */
136           raiserror 17590
137           return (1)
138       end
139   
140       /*
141       **  Make sure that we are in the database specified
142       **  by @dbname. 
143       */
144       if @dbname != db_name()
145       begin
146           /* 
147           ** 13232, "drop"
148           ** 17288, "You must execute this procedure from the database 
149           ** 	  in which you wish to %1! a segment.  Please execute 
150           **	  'use %2!' and try again."
151           */
152           declare @action varchar(30)
153           select @action = description
154           from master.dbo.sysmessages
155           where error = 13232 and langid = @@langid
156           if @action is null
157               select @action = description
158               from master.dbo.sysmessages
159               where error = 13232 and langid is null
160           if @action is null select @action = "drop"
161           raiserror 17288, @action, @dbname
162           return (1)
163       end
164   
165       select @dbid = db_id(@dbname)
166   
167       /*
168       ** Determine if we are dealing with a temporary database.
169       */
170       select @tempdb_mask = number
171       from master.dbo.spt_values
172       where type = "D3" and name = "TEMPDB STATUS MASK"
173   
174       if (@dbid = 2) or exists (select * from master.dbo.sysdatabases
175               where dbid = @dbid
176                   and (status3 & @tempdb_mask) != 0)
177       begin
178           select @isatempdb = 1
179       end
180       else
181       begin
182           select @isatempdb = 0
183       end
184   
185       select @isamaster = 0
186       if ((@dbid = db_id("master"))
187               or (@dbid = db_id("master_companion")))
188       begin
189           select @isamaster = 1
190       end
191   
192       /*
193       **  Check to see if the segment exists.
194       */
195       if not exists (select *
196               from syssegments
197               where name = @segname)
198       begin
199           /*
200           ** 17520, "There is no such segment as '%1!'."
201           */
202           raiserror 17520, @segname
203           return (1)
204       end
205   
206       /*
207       **  Get the segment number of @segname.
208       */
209       select @segbit = segment
210       from syssegments
211       where name = @segname
212   
213       select @segnum = @segbit
214   
215       /*
216       **  Now convert the segment number to the right bit for segmap.
217       */
218       if (@segbit < 31)
219           select @segbit = power(2, @segbit)
220       else
221           /*
222           **  Since this is segment 31, power(2, 31) will overflow
223           **  since segmap is an int.  We'll grab the machine-dependent
224           **  bit mask from spt_values to clear the right bit.
225           */
226           select @segbit = low
227           from master.dbo.spt_values
228           where type = "E"
229               and number = 2
230   
231       /*
232       **  Don't allow the segments 'default', 'system', or 'logsegment' to
233       **  be dropped completely.
234       */
235       if @segname in ("system", "default", "logsegment")
236       begin
237           if @device is null
238           begin
239               /*
240               ** 17521, "Can't drop the '%1!'segment completely."
241               */
242               raiserror 17521, @segname
243               return (1)
244           end
245   
246       /*
247       **  For the case where we are dropping the logsegment from a 
248       **  device, the logic below works OK.  If this is the last
249       **  device for the the segment, it won't let the segment be
250       **  dropped.
251       */
252       end
253   
254       /* If we are removing log space and this is not a temporary database ... */
255       if @segbit = 4 and @isatempdb = 0
256       begin
257           /* 
258           ** ... check single user bit (4096).
259           ** The database must be in single user mode so that no log allocation
260           ** or deallocation take place while looking for possibly stranded 
261           ** objects, and while recalculating the free space counter.
262           */
263           select @bitdesc = null
264           select @bitdesc = v.name
265           from master.dbo.spt_values v, master.dbo.sysdatabases d
266           where d.dbid = @dbid
267               and v.type = "D"
268               and d.status & v.number = 4096
269           if @bitdesc is null
270           begin
271               /*
272               ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'."
273               */
274               raiserror 17793, @dbname, "sp_dropsegment"
275               return (1)
276           end
277       end
278   
279       /*
280       **  If we are dropping the segment, check to see that the segment isn't
281       **  being used by anyone.
282       */
283       if @device is null
284       begin
285   
286           /*
287           **  Check to see if the segment is being used.
288           */
289           if exists (select *
290                   from sysindexes i, syssegments s, syspartitions p
291                   where s.name = @segname
292                       and (i.segment = s.segment or
293                           p.segment = s.segment))
294           begin
295               /*
296               ** 17522, "The segment '%1!' is being used."
297               */
298               raiserror 17522, @segname
299               return (1)
300           end
301       end
302   
303       /*
304       **  If we are dropping the segment reference, check to see that arguments are
305       **  reasonable.
306       */
307       if @device is not null
308       begin
309           /*
310           **  Now see if the database even uses the @device
311           */
312           if not exists (select *
313                   from master.dbo.sysusages u, master.dbo.sysdevices d
314                   where d.name = @device
315                       and u.vdevno = d.vdevno
316                       and u.dbid = @dbid)
317           begin
318               /*
319               ** 17281, "The specified device is not used by the database."
320               */
321               raiserror 17281
322   
323   
324   
325               return (1)
326           end
327   
328           /*
329           **  Now check that the segment references the device.
330           */
331           if not exists (select *
332                   from master.dbo.sysusages u, master.dbo.sysdevices d
333                   where u.segmap & @segbit = @segbit
334                       and d.name = @device
335                       and u.vdevno = d.vdevno
336                       and u.dbid = @dbid
337                       and ((d.status & 2 = 2) or (d.status2 & 8 = 8)))
338           begin
339               /*
340               ** 17523, "Segment '%1!' does not reference device '%2!'."
341               */
342               raiserror 17523, @segname, @device
343               return (1)
344           end
345   
346           /*
347           **  Now check to see if this is the last unique device reference for the
348           **  segment.  If it is then we don't want to drop the reference.
349           */
350           select distinct d.name into #temptable
351           from master.dbo.sysusages u, master.dbo.sysdevices d
352           where u.segmap & @segbit = @segbit
353               and u.vdevno = d.vdevno
354               and u.dbid = @dbid
355               and ((d.status & 2 = 2) or (d.status2 & 8 = 8))
356   
357           if (select count(*) from #temptable) <= 1
358           begin
359               /*
360               ** 17524, "There is only one device mapping for the segment '%1!' -- use sp_dropsegment with no device argument."
361               */
362               raiserror 17524, @segname
363               return (1)
364           end
365   
366           drop table #temptable
367       end
368   
369       /*
370       **  If we're dropping the segment, go ahead drop the segment and
371       **  clear its bit in sysusages.
372       */
373       if @device is null
374       begin
375           /*
376           **  Remove any threshold references
377           */
378           if exists (select * from sysobjects where name = "systhresholds")
379               delete systhresholds
380               from systhresholds t, syssegments s
381               where s.name = @segname
382                   and s.segment = t.segment
383   
384           /*
385           **  NOTE: Don't update master.dbo.sysusages and syssegments as a xact 
386           **  	since it could cause problems for recovery.
387           */
388   
389           delete syssegments
390           where name = @segname
391   
392           /* Encapsulate sysusages/anchors update in a transaction */
393           if (@isamaster = 1)
394               begin transaction sysusg_upd
395   
396           /*
397           **  Now clear the segment from device in sysusages.
398           */
399           update master.dbo.sysusages
400           set segmap = segmap & (~ @segbit)
401           from master.dbo.sysusages u, master.dbo.sysdevices d
402           where u.vdevno = d.vdevno
403               and u.dbid = @dbid
404               and ((d.status & 2 = 2)
405                   or (d.status2 & 8 = 8))
406       end
407   
408       /*
409       **  We want to just clear the segment reference from the device.
410       */
411       else
412       begin
413           /* Encapsulate sysusages/anchors update in a transaction */
414           if (@isamaster = 1)
415               begin transaction sysusg_upd
416   
417           update master.dbo.sysusages
418           set segmap = segmap & (~ @segbit)
419           from master.dbo.sysusages u, master.dbo.sysdevices d
420           where u.vdevno = d.vdevno
421               and u.dbid = @dbid
422               and ((d.status & 2 = 2)
423                   or (d.status2 & 8 = 8))
424               and d.name = @device
425   
426           /*
427           **  Remove any threshold references that exceed the remaining
428           **  size of the segment
429           */
430           if exists (select * from sysobjects where name = "systhresholds")
431               delete systhresholds
432               from systhresholds t, syssegments s
433               where s.name = @segname
434                   and s.segment = t.segment
435                   and t.free_space >= (select sum(size)
436                       from master.dbo.sysusages
437                       where dbid = @dbid
438                           and segmap & @segbit = @segbit)
439       end
440   
441       if (@isamaster = 1)
442       begin
443           if (@@error != 0)
444           begin
445               rollback tran sysusg_upd
446               return (1)
447           end
448   
449           dbcc dbrepair(@dbname, "upd_usg")
450           if (@@error != 0)
451           begin
452               rollback tran sysusg_upd
453               return (1)
454           end
455   
456           commit transaction sysusg_upd
457       end
458   
459       /*
460       **  Now we need to activate the new segment map.
461       */
462       dbcc dbrepair(@dbname, remap, NULL, @segnum, @segname)
463   
464       /*
465       ** By removing a piece of the log segment, we may have cleared a stranded
466       ** object condition, so we need to look for stranded objects.  We can also 
467       ** take this opportunity to recalculate the log's last-chance threshold.
468       */
469       if @segbit = 4
470       begin
471           dbcc dbrepair(@dbname, findstranded, NULL, @isatempdb)
472           select @lct = lct_admin("lastchance", @dbid)
473           if @lct > 0
474               exec sp_getmessage 17715, @msg out
475           else
476               exec sp_getmessage 17716, @msg out
477   
478           print @msg, @dbname, @lct
479       end
480   
481       if @device is null
482       begin
483           /*
484           ** 17525, "Segment dropped."
485           */
486           exec sp_getmessage 17525, @msg output
487           print @msg
488       end
489       else
490       begin
491           /*
492           ** 17526, "Segment reference to device dropped."
493           */
494           exec sp_getmessage 17526, @msg output
495           print @msg
496       end
497   
498       /*
499       **  If there are no more segment references to the device,
500       **  print a warning.
501       */
502       if exists (select *
503               from master.dbo.sysusages
504               where segmap = 0
505                   and dbid = @dbid)
506       begin
507           declare @nosegs varchar(255), /* list of unreferenced devices */
508               @curdevice varchar(30),
509               @i int
510   
511           select @curdevice = min(d.name)
512           from master.dbo.sysusages u, master.dbo.sysdevices d
513           where u.vdevno = d.vdevno
514               and u.dbid = @dbid
515               and ((d.status & 2 = 2) or (d.status2 & 8 = 8))
516               and segmap = 0
517           select @nosegs = @curdevice, @i = 1
518           while (@curdevice is not null)
519           begin
520               select @curdevice = min(d.name)
521               from master.dbo.sysusages u, master.dbo.sysdevices d
522               where u.vdevno = d.vdevno
523                   and u.dbid = @dbid
524                   and ((d.status & 2 = 2)
525                       or (d.status2 & 8 = 8))
526                   and segmap = 0
527                   and d.name > @curdevice
528               if @curdevice is not null
529               begin
530                   select @nosegs = @nosegs + ", " + @curdevice
531                   select @i = @i + 1
532               end
533           end
534   
535           /*
536           **  Get the device names.
537           */
538           if @i > 1
539               /*
540               ** 17528, "WARNING: There are no longer any segments referencing devices '%1!'.  These devices will no longer be used for space allocation."
541               */
542               exec sp_getmessage 17528, @msg output
543           else
544               /*
545               ** 17527, "WARNING: There are no longer any segments referencing device '%1!'.  This device will no longer be used for space allocation."
546               */
547               exec sp_getmessage 17527, @msg output
548           print @msg, @nosegs
549       end
550       return (0)
551   


exec sp_procxmode 'sp_dropsegment', 'AnyMode'
go

Grant Execute on sp_dropsegment to public
go
DEFECTS
 MRIT 5 Return in Transaction trancount is 1 550
 QJWI 5 Join or Sarg Without Index 268
 QJWI 5 Join or Sarg Without Index 292
 QJWI 5 Join or Sarg Without Index 293
 QJWI 5 Join or Sarg Without Index 382
 QJWI 5 Join or Sarg Without Index 434
 MCTR 4 Conditional Begin Tran or Commit Tran 394
 MCTR 4 Conditional Begin Tran or Commit Tran 415
 MCTR 4 Conditional Begin Tran or Commit Tran 456
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @action: varchar(30) = varchar(1024) 153
 MTYP 4 Assignment type mismatch @action: varchar(30) = varchar(1024) 157
 MTYP 4 Assignment type mismatch @bitdesc: varchar(30) = varchar(255) 264
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
172
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
267
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 155
 QTYP 4 Comparison type mismatch smallint = int 155
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 TNOI 4 Table with no index sybsystemprocs..systhresholds sybsystemprocs..systhresholds
 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 master..sysdatabases  
 MGTP 3 Grant to public master..sysdevices  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public master..sysusages  
 MGTP 3 Grant to public sybsystemprocs..sp_dropsegment  
 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..syssegments  
 MGTP 3 Grant to public sybsystemprocs..systhresholds  
 MNER 3 No Error Check should check @@error after select into 350
 MNER 3 No Error Check should check @@error after delete 379
 MNER 3 No Error Check should check @@error after delete 389
 MNER 3 No Error Check should check @@error after update 399
 MNER 3 No Error Check should check @@error after update 417
 MNER 3 No Error Check should check @@error after delete 431
 MNER 3 No Error Check should check return value of exec 474
 MNER 3 No Error Check should check return value of exec 476
 MNER 3 No Error Check should check return value of exec 486
 MNER 3 No Error Check should check return value of exec 494
 MNER 3 No Error Check should check return value of exec 542
 MNER 3 No Error Check should check return value of exec 547
 MUBC 3 Unbalanced begin tran/commit tran 456
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 162
 MUCO 3 Useless Code Useless Brackets 186
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 218
 MUCO 3 Useless Code Useless Brackets 243
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 299
 MUCO 3 Useless Code Useless Brackets 325
 MUCO 3 Useless Code Useless Brackets 343
 MUCO 3 Useless Code Useless Brackets 363
 MUCO 3 Useless Code Useless Brackets 393
 MUCO 3 Useless Code Useless Brackets 400
 MUCO 3 Useless Code Useless Brackets 414
 MUCO 3 Useless Code Useless Brackets 418
 MUCO 3 Useless Code Useless Brackets 441
 MUCO 3 Useless Code Useless Brackets 443
 MUCO 3 Useless Code Useless Brackets 446
 MUCO 3 Useless Code Useless Brackets 450
 MUCO 3 Useless Code Useless Brackets 453
 MUCO 3 Useless Code Useless Brackets 518
 MUCO 3 Useless Code Useless Brackets 550
 MUOT 3 Updates outside transaction 389
 QAFM 3 Var Assignment from potentially many rows 153
 QAFM 3 Var Assignment from potentially many rows 157
 QAFM 3 Var Assignment from potentially many rows 170
 QAFM 3 Var Assignment from potentially many rows 209
 QAFM 3 Var Assignment from potentially many rows 226
 QCTC 3 Conditional Table Creation 350
 QDIS 3 Check correct use of 'select distinct' 350
 QGWO 3 Group by/Distinct/Union without order by 350
 QISO 3 Set isolation level 71
 QNAJ 3 Not using ANSI Inner Join 265
 QNAJ 3 Not using ANSI Inner Join 290
 QNAJ 3 Not using ANSI Inner Join 313
 QNAJ 3 Not using ANSI Inner Join 332
 QNAJ 3 Not using ANSI Inner Join 351
 QNAJ 3 Not using ANSI Inner Join 380
 QNAJ 3 Not using ANSI Inner Join 401
 QNAJ 3 Not using ANSI Inner Join 419
 QNAJ 3 Not using ANSI Inner Join 432
 QNAJ 3 Not using ANSI Inner Join 512
 QNAJ 3 Not using ANSI Inner Join 521
 QNUA 3 Should use Alias: Column segmap should use alias u 400
 QNUA 3 Should use Alias: Column segmap should use alias u 418
 QNUA 3 Should use Alias: Table master..sysusages 436
 QNUA 3 Should use Alias: Column segmap should use alias u 516
 QNUA 3 Should use Alias: Column segmap should use alias u 526
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
155
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
159
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
316
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
333
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
352
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
378
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
403
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
421
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
430
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
437
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
504
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
514
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
523
 QTJ1 3 Table only appears in inner join clause 379
 VNRD 3 Variable is not read @procval 51
 VNRD 3 Variable is not read @status1 118
 VUNU 3 Variable is not used @dbuid 35
 MSUB 2 Subquery Marker 132
 MSUB 2 Subquery Marker 174
 MSUB 2 Subquery Marker 195
 MSUB 2 Subquery Marker 289
 MSUB 2 Subquery Marker 312
 MSUB 2 Subquery Marker 331
 MSUB 2 Subquery Marker 378
 MSUB 2 Subquery Marker 430
 MSUB 2 Subquery Marker 435
 MSUB 2 Subquery Marker 502
 MTR1 2 Metrics: Comments Ratio Comments: 44% 29
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 60 = 73dec - 15exi + 2 29
 MTR3 2 Metrics: Query Complexity Complexity: 288 29
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0 289
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 312
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 331
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 350
 PRED_QUERY_COLLECTION 2 {s=sybsystemprocs..syssegments, t=sybsystemprocs..systhresholds} 0 379
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 399
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 417
 PRED_QUERY_COLLECTION 2 {s=sybsystemprocs..syssegments, t=sybsystemprocs..systhresholds} 0 431
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 511
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 520

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysindexes  
reads table sybsystemprocs..sysobjects  
read_writes table master..sysusages (1)  
reads table master..sysdevices (1)  
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..spt_values (1)  
writes table sybsystemprocs..systhresholds  
read_writes table tempdb..#temptable (1) 
reads table sybsystemprocs..syspartitions  
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
reads table master..sysmessages (1)  
read_writes table sybsystemprocs..syssegments