DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpsegment  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_helpsegment
3         @segname varchar(255) = NULL /* segment name */
4     as
5     
6         declare @segbit int, /* this is the bit version of the segment # */
7             @segment int, /* the segment number of the segment */
8             @free_pages bigint, /* unused pages in segment */
9             @factor float, /* conversion factor to convert to MB */
10            @clr_pages bigint, /* Space reserved for CLRs */
11            @total_pages bigint, /* total allocatable log space */
12            @used_pages bigint, /* allocated log space */
13            @used_pages_wo_APs /* allocated log space without inc */
14            bigint, /* allocation pages */
15            @msg varchar(1024) /* message text */
16    
17    
18        if @@trancount = 0
19        begin
20            set chained off
21        end
22    
23        set transaction isolation level 1
24    
25        set nocount on
26    
27        /*
28        **  If no segment name given, get 'em all.
29        */
30        if @segname is null
31        begin
32            exec sp_autoformat @fulltabname = "syssegments",
33                @orderby = "order by segment"
34            return (0)
35        end
36    
37        /*
38        **  Make sure the segment exists
39        */
40        if not exists (select *
41                from syssegments
42                where name = @segname)
43        begin
44            /* 17520, "There is no such segment as '%1!'." */
45            raiserror 17520, @segname
46            return (1)
47        end
48    
49        /*
50        **  Show the syssegment entry, then the fragments and size it is on,
51        **  then any dependent objects in the database.
52        */
53        /* Adaptive Server has expanded all '*' elements in the following statement */ select syssegments.segment, syssegments.name, syssegments.status
54        into #sphelpsegment2rs
55        from syssegments
56        where name = @segname
57        exec sp_autoformat @fulltabname = #sphelpsegment2rs
58        drop table #sphelpsegment2rs
59        /*
60        **  Set the bit position for the segment.
61        */
62        select @segment = segment
63        from syssegments
64        where name = @segname
65    
66        /*
67        **  Now set the segments on @devname sysusages.
68        */
69        if (@segment < 31)
70            select @segbit = power(2, @segment)
71        else
72            /*
73            **  Since this is segment 31, power(2, 31) will overflow
74            **  since segmap is an int.  We'll grab the machine-dependent
75            **  bit mask from spt_values to set the right bit.
76            */
77            select @segbit = low
78            from master.dbo.spt_values
79            where type = "E"
80                and number = 2
81    
82        /*
83        ** Get factor for conversion of pages to megabytes from spt_values
84        */
85        select @factor = convert(float, low) / 1048576.0
86        from master.dbo.spt_values
87        where number = 1 and type = "E"
88    
89        select @total_pages = sum(u.size)
90        from master.dbo.sysusages u
91        where u.segmap & @segbit = @segbit
92            and u.dbid = db_id()
93    
94    
95        /*
96        ** Select the sizes of the segments
97        */
98        if (@segbit = 4)
99        begin
100           select device = d.name,
101               size = convert(varchar(20), round((sum(u.size) * @factor), 0)) + "MB"
102           into #sphelpsegment3rs
103           from master.dbo.sysusages u, master.dbo.sysdevices d
104           where u.segmap & @segbit = @segbit
105               and u.dbid = db_id()
106               and ((d.status & 2 = 2) or (d.status2 & 8 = 8))
107               and u.vdevno = d.vdevno
108           group by d.name
109           exec sp_autoformat @fulltabname = #sphelpsegment3rs,
110               @orderby = "order by 1"
111           drop table #sphelpsegment3rs
112   
113           -- To avoid errors while creating this sproc because
114           -- sp_spaceused appears later in the create list.
115           -- execute the procedure in the current user database's context
116           set @msg = "sp_spaceused_syslogs"
117           exec @msg @total_pages output
118               , @free_pages output
119               , @used_pages output
120               , @used_pages_wo_APs output
121               , @clr_pages output
122   
123       end
124       else
125       begin
126           select device = d.name,
127               size = convert(varchar(20), round((sum(u.size) * @factor), 0)) + "MB",
128               free_pages = sum(curunreservedpgs(db_id(), u.lstart, u.unreservedpgs))
129           into #sphelpsegment4rs
130           from master.dbo.sysusages u, master.dbo.sysdevices d
131           where u.segmap & @segbit = @segbit
132               and u.dbid = db_id()
133               and ((d.status & 2 = 2) or (d.status2 & 8 = 8))
134               and u.vdevno = d.vdevno
135           group by d.name
136   
137           exec sp_autoformat @fulltabname = #sphelpsegment4rs,
138               @orderby = "order by 1"
139   
140           drop table #sphelpsegment4rs
141   
142           select @free_pages = sum(curunreservedpgs(db_id(), u.lstart, u.unreservedpgs))
143           from master.dbo.sysusages u
144           where u.segmap & @segbit = @segbit
145               and u.dbid = db_id()
146   
147           select @used_pages = @total_pages - @free_pages
148           select @clr_pages = 0
149       end
150   
151       /*
152       ** Select the dependent objects
153       ** The segment information for a table is stored at both
154       ** sysindexes and syspartitions. The segment in syspartitions
155       ** tells where the future location of data in this partition.
156       ** The segment in sysindexes is the default segment specified
157       ** for the whole table/index. Any partitions under this table/
158       ** index that doesn't have the segment specification for its
159       ** own will use this default segment in sysindexes.
160       */
161       if (@segname = 'logsegment')
162       begin
163           print " "
164           /* 19342, "Objects on segment '%1!':" */
165           exec sp_getmessage 19342, @msg output
166           print @msg, @segname
167           print " "
168   
169           /*
170           ** Do some special-handling for logsegment. We know that only syslogs
171           ** will reside on it, so hard-code the id/indid in the WHERE clause
172           ** below. One reason why we do this is that if you run this sproc on
173           ** logsegment when a large utility (e.g. ALTER LOCK CHANGE) is going on
174           ** concurrently, some times we end up with the scan on syspartitions
175           ** below being blocked by the large utility. To avoid that, and to get
176           ** the results of the objects bound to logsegment, do this hard-path
177           ** short cut.
178           */
179           select table_name = object_name(p.id), index_name = i.name, i.indid,
180               partition_name = p.name
181           into #result3
182           from sysindexes i, syssegments s, syspartitions p
183           where s.name = @segname
184               and s.segment = p.segment
185               and p.id = i.id
186               and p.indid = i.indid
187               and p.id = object_id('syslogs')
188               and p.indid = 0
189   
190           exec sp_autoformat @fulltabname = '#result3', @orderby = 'order by 1,3,4'
191       end
192   
193       else if exists (select *
194               from syspartitions p, syssegments s
195               where s.name = @segname
196                   and s.segment = p.segment)
197       begin
198           print " "
199           /* 19342, "Objects on segment '%1!':" */
200           exec sp_getmessage 19342, @msg output
201           print @msg, @segname
202           print " "
203   
204           select table_name = object_name(p.id), index_name = i.name, i.indid,
205               partition_name = p.name
206           into #result1
207           from sysindexes i, syssegments s, syspartitions p
208           where s.name = @segname
209               and s.segment = p.segment
210               and p.id = i.id
211               and p.indid = i.indid
212   
213           exec sp_autoformat @fulltabname = '#result1', @orderby = 'order by 1,3,4'
214       end
215   
216   
217       if exists (select *
218               from syssegments s, sysindexes i
219               where s.name = @segname
220                   and s.segment = i.segment)
221       begin
222           print " "
223           /* 19341, "Objects currently bound to segment '%1!':" */
224           exec sp_getmessage 19341, @msg output
225           print @msg, @segname
226           print " "
227   
228           select table_name = object_name(i.id), index_name = i.name, i.indid
229           into #result2
230           from sysindexes i, syssegments s
231           where s.name = @segname
232               and s.segment = i.segment
233   
234           exec sp_autoformat @fulltabname = '#result2', @orderby = 'order by 1,3'
235           print " "
236       end
237   
238       /*
239       ** Print total_size, total_pages, free_pages, used_pages and reserved_pages
240       */
241   
242       select total_size = convert(varchar(15),
243           round(@total_pages * @factor, 0)) + "MB",
244           total_pages = convert(char(15), @total_pages),
245           free_pages = convert(char(15), @free_pages),
246           used_pages = convert(char(15), @used_pages),
247           reserved_pages = convert(char(15), @clr_pages)
248   
249       return (0)
250   
251   


exec sp_procxmode 'sp_helpsegment', 'AnyMode'
go

Grant Execute on sp_helpsegment to public
go
RESULT SETS
sp_helpsegment_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 184
 QJWI 5 Join or Sarg Without Index 196
 QJWI 5 Join or Sarg Without Index 209
 QJWI 5 Join or Sarg Without Index 220
 QJWI 5 Join or Sarg Without Index 232
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 57
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 109
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 137
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 92
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 105
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 132
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 145
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 188
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 179
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 185
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 185
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 187
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 204
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 210
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitions.id: Warning message on syspartitions 210
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 228
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdevices  
 MGTP 3 Grant to public master..sysusages  
 MGTP 3 Grant to public sybsystemprocs..sp_helpsegment  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..syspartitions  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MNER 3 No Error Check should check return value of exec 32
 MNER 3 No Error Check should check @@error after select into 53
 MNER 3 No Error Check should check return value of exec 57
 MNER 3 No Error Check should check @@error after select into 100
 MNER 3 No Error Check should check return value of exec 109
 MNER 3 No Error Check should check @@error after select into 126
 MNER 3 No Error Check should check return value of exec 137
 MNER 3 No Error Check should check return value of exec 165
 MNER 3 No Error Check should check @@error after select into 179
 MNER 3 No Error Check should check return value of exec 190
 MNER 3 No Error Check should check return value of exec 200
 MNER 3 No Error Check should check @@error after select into 204
 MNER 3 No Error Check should check return value of exec 213
 MNER 3 No Error Check should check return value of exec 224
 MNER 3 No Error Check should check @@error after select into 228
 MNER 3 No Error Check should check return value of exec 234
 MUCO 3 Useless Code Useless Brackets 34
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 249
 QAFM 3 Var Assignment from potentially many rows 62
 QAFM 3 Var Assignment from potentially many rows 77
 QAFM 3 Var Assignment from potentially many rows 85
 QCTC 3 Conditional Table Creation 100
 QCTC 3 Conditional Table Creation 126
 QCTC 3 Conditional Table Creation 179
 QCTC 3 Conditional Table Creation 204
 QCTC 3 Conditional Table Creation 228
 QISO 3 Set isolation level 23
 QNAJ 3 Not using ANSI Inner Join 103
 QNAJ 3 Not using ANSI Inner Join 130
 QNAJ 3 Not using ANSI Inner Join 182
 QNAJ 3 Not using ANSI Inner Join 194
 QNAJ 3 Not using ANSI Inner Join 207
 QNAJ 3 Not using ANSI Inner Join 218
 QNAJ 3 Not using ANSI Inner Join 230
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
91
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
104
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
131
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
144
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syspartitions.csyspartitions unique clustered
(id, indid, partitionid)
Intersection: {indid, id}
187
 VNRD 3 Variable is not read @used_pages_wo_APs 120
 MDYE 2 Dynamic Exec Marker exec @msg 117
 MRST 2 Result Set Marker 242
 MSUB 2 Subquery Marker 40
 MSUB 2 Subquery Marker 193
 MSUB 2 Subquery Marker 217
 MTR1 2 Metrics: Comments Ratio Comments: 33% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 31 = 32dec - 3exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 131 2
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 100
 PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 126
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0 179
 PRED_QUERY_COLLECTION 2 {p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0 193
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0 204
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, s=sybsystemprocs..syssegments} 0 217
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, s=sybsystemprocs..syssegments} 0 228

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysusages (1)  
writes table tempdb..#sphelpsegment4rs (1) 
reads table sybsystemprocs..syssegments  
writes table tempdb..#result2 (1) 
writes table tempdb..#sphelpsegment2rs (1) 
reads table sybsystemprocs..sysindexes  
calls proc sybsystemprocs..sp_spaceused_syslogs  
   reads table master..sysusages (1)  
   reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
writes table sybsystemprocs..sp_helpsegment_rset_001 
writes table tempdb..#result1 (1) 
reads table sybsystemprocs..syspartitions  
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_005 
   writes table sybsystemprocs..sp_autoformat_rset_002 
   calls proc sybsystemprocs..sp_namecrack  
   reads table master..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   writes table sybsystemprocs..sp_autoformat_rset_004 
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
reads table master..sysdevices (1)  
writes table tempdb..#sphelpsegment3rs (1) 
reads table master..spt_values (1)  
writes table tempdb..#result3 (1)