1
2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3 /* 4.8 1.1 06/14/90 sproc/src/fixindex */
4 /*
5 ** Messages for "sp_helpsegment" 17nnn
6 **
7 ** 17520, "There is no such segment as '%1!'."
8 ** 19341, "Objects currently bond to segment '%1!':"
9 ** 19342, "Objects on segment '%1!':"
10 */
11 create procedure sp_helpsegment
12 @segname varchar(255) = NULL /* segment name */
13 as
14
15 declare @segbit int, /* this is the bit version of the segment # */
16 @segment int, /* the segment number of the segment */
17 @free_pages int, /* unused pages in segment */
18 @factor float, /* conversion factor to convert to MB */
19 @clr_pages int, /* Space reserved for CLRs */
20 @total_pages int, /* total allocatable log space */
21 @used_pages int, /* allocated log space */
22 @ismixedlog int, /* mixed log & data database ? */
23 @msg varchar(1024) /* message text */
24
25
26 if @@trancount = 0
27 begin
28 set chained off
29 end
30
31 set transaction isolation level 1
32
33 set nocount on
34
35 /*
36 ** If no segment name given, get 'em all.
37 */
38 if @segname is null
39 begin
40 exec sp_autoformat @fulltabname = "syssegments",
41 @orderby = "order by segment"
42 return (0)
43 end
44
45 /*
46 ** Make sure the segment exists
47 */
48 if not exists (select *
49 from syssegments
50 where name = @segname)
51 begin
52 /* 17520, "There is no such segment as '%1!'." */
53 raiserror 17520, @segname
54 return (1)
55 end
56
57 /*
58 ** Show the syssegment entry, then the fragments and size it is on,
59 ** then any dependent objects in the database.
60 */
61 /* Adaptive Server has expanded all '*' elements in the following statement */ select syssegments.segment, syssegments.name, syssegments.status
62 into #sphelpsegment2rs
63 from syssegments
64 where name = @segname
65 exec sp_autoformat @fulltabname = #sphelpsegment2rs
66 drop table #sphelpsegment2rs
67 /*
68 ** Set the bit position for the segment.
69 */
70 select @segment = segment
71 from syssegments
72 where name = @segname
73
74 /*
75 ** Now set the segments on @devname sysusages.
76 */
77 if (@segment < 31)
78 select @segbit = power(2, @segment)
79 else
80 /*
81 ** Since this is segment 31, power(2, 31) will overflow
82 ** since segmap is an int. We'll grab the machine-dependent
83 ** bit mask from spt_values to set the right bit.
84 */
85 select @segbit = low
86 from master.dbo.spt_values
87 where type = "E"
88 and number = 2
89
90 /*
91 ** Get factor for conversion of pages to megabytes from spt_values
92 */
93 select @factor = convert(float, low) / 1048576.0
94 from master.dbo.spt_values
95 where number = 1 and type = "E"
96
97 select @total_pages = sum(u.size)
98 from master.dbo.sysusages u
99 where u.segmap & @segbit = @segbit
100 and u.dbid = db_id()
101
102 select @ismixedlog = status2 & 32768
103 from master.dbo.sysdatabases where dbid = db_id()
104
105 /*
106 ** Select the sizes of the segments
107 */
108 if (@segbit = 4)
109 begin
110 select device = d.name,
111 size = convert(varchar(20), round((sum(u.size) * @factor), 0)) + "MB"
112 into #sphelpsegment3rs
113 from master.dbo.sysusages u, master.dbo.sysdevices d
114 where u.segmap & @segbit = @segbit
115 and u.dbid = db_id()
116 and ((d.status & 2 = 2) or (d.status2 & 8 = 8))
117 and u.vdevno = d.vdevno
118 group by d.name
119 exec sp_autoformat @fulltabname = #sphelpsegment3rs,
120 @orderby = "order by 1"
121 drop table #sphelpsegment3rs
122
123 select @clr_pages = lct_admin("reserved_for_rollbacks", db_id())
124 select @free_pages = lct_admin("logsegment_freepages", db_id())
125 - @clr_pages
126
127 select free_pages = @free_pages
128
129 if (@ismixedlog = 32768)
130 begin
131 /*
132 ** For a mixed log and data database, we cannot
133 ** deduce the log used space from the total space
134 ** as it is mixed with data. So we take the expensive
135 ** way by scanning syslogs.
136 */
137 select @used_pages = lct_admin("num_logpages", db_id())
138
139 /* Account allocation pages as used pages */
140 select @used_pages = @used_pages + (@total_pages / 256)
141 end
142 else
143 begin
144 /* Dedicated log database */
145 select @used_pages = @total_pages - @free_pages
146 - @clr_pages
147 end
148 end
149 else
150 begin
151 select device = d.name,
152 size = convert(varchar(20), round((sum(u.size) * @factor), 0)) + "MB",
153 free_pages = sum(curunreservedpgs(db_id(), u.lstart, u.unreservedpgs))
154 into #sphelpsegment4rs
155 from master.dbo.sysusages u, master.dbo.sysdevices d
156 where u.segmap & @segbit = @segbit
157 and u.dbid = db_id()
158 and ((d.status & 2 = 2) or (d.status2 & 8 = 8))
159 and u.vdevno = d.vdevno
160 group by d.name
161 exec sp_autoformat @fulltabname = #sphelpsegment4rs,
162 @orderby = "order by 1"
163 drop table #sphelpsegment4rs
164 select @free_pages = sum(curunreservedpgs(db_id(), u.lstart, u.unreservedpgs))
165 from master.dbo.sysusages u
166 where u.segmap & @segbit = @segbit
167 and u.dbid = db_id()
168
169 select @used_pages = @total_pages - @free_pages
170 select @clr_pages = 0
171 end
172
173 /*
174 ** Select the dependent objects
175 ** The segment information for a table is stored at both
176 ** sysindexes and syspartitions. The segment in syspartitions
177 ** tells where the future location of data in this partition.
178 ** The segment in sysindexes is the default segment specified
179 ** for the whole table/index. Any partitions under this table/
180 ** index that doesn't have the segment specification for its
181 ** own will use this default segment in sysindexes.
182 */
183 if exists (select *
184 from syspartitions p, syssegments s
185 where s.name = @segname
186 and s.segment = p.segment)
187 begin
188 print " "
189 /* 19342, "Objects on segment '%1!':" */
190 exec sp_getmessage 19342, @msg output
191 print @msg, @segname
192 print " "
193 select table_name = object_name(p.id), index_name = i.name, i.indid,
194 partition_name = p.name
195 into #result1
196 from sysindexes i, syssegments s, syspartitions p
197 where s.name = @segname
198 and s.segment = p.segment
199 and p.id = i.id
200 and p.indid = i.indid
201 exec sp_autoformat @fulltabname = '#result1', @orderby = 'order by 1,3,4'
202 end
203
204
205 if exists (select *
206 from syssegments s, sysindexes i
207 where s.name = @segname
208 and s.segment = i.segment)
209 begin
210 print " "
211 /* 19341, "Objects currently bond to segment '%1!':" */
212 exec sp_getmessage 19341, @msg output
213 print @msg, @segname
214 print " "
215 select table_name = object_name(i.id), index_name = i.name, i.indid
216 into #result2
217 from sysindexes i, syssegments s
218 where s.name = @segname
219 and s.segment = i.segment
220 exec sp_autoformat @fulltabname = '#result2', @orderby = 'order by 1,3'
221 print " "
222 end
223
224 /*
225 ** Print total_size, total_pages, free_pages, used_pages and reserved_pages
226 */
227
228 select total_size = convert(varchar(15),
229 round(@total_pages * @factor, 0)) + "MB",
230 total_pages = convert(char(15), @total_pages),
231 free_pages = convert(char(15), @free_pages),
232 used_pages = convert(char(15), @used_pages),
233 reserved_pages = convert(char(15), @clr_pages)
234
235 return (0)
236
exec sp_procxmode 'sp_helpsegment', 'AnyMode'
go
Grant Execute on sp_helpsegment to public
go
| DEFECTS |
 QJWI 5 Join or Sarg Without Index |
186 |
| QJWI 5 Join or Sarg Without Index |
198 |
| QJWI 5 Join or Sarg Without Index |
208 |
| QJWI 5 Join or Sarg Without Index |
219 |
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname |
65 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname |
119 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname |
161 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
100 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
103 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
115 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
157 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
167 |
| TNOI 4 Table with no index sybsystemprocs..syssegments |
sybsystemprocs..syssegments |
| 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..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 |
40 |
| MNER 3 No Error Check should check @@error after select into |
61 |
| MNER 3 No Error Check should check return value of exec |
65 |
| MNER 3 No Error Check should check @@error after select into |
110 |
| MNER 3 No Error Check should check return value of exec |
119 |
| MNER 3 No Error Check should check @@error after select into |
151 |
| MNER 3 No Error Check should check return value of exec |
161 |
| MNER 3 No Error Check should check return value of exec |
190 |
| MNER 3 No Error Check should check @@error after select into |
193 |
| MNER 3 No Error Check should check return value of exec |
201 |
| MNER 3 No Error Check should check return value of exec |
212 |
| MNER 3 No Error Check should check @@error after select into |
215 |
| MNER 3 No Error Check should check return value of exec |
220 |
| MUCO 3 Useless Code Useless Brackets |
42 |
| MUCO 3 Useless Code Useless Brackets |
54 |
| MUCO 3 Useless Code Useless Brackets |
77 |
| MUCO 3 Useless Code Useless Brackets |
108 |
| MUCO 3 Useless Code Useless Brackets |
111 |
| MUCO 3 Useless Code Useless Brackets |
129 |
| MUCO 3 Useless Code Useless Brackets |
140 |
| MUCO 3 Useless Code Useless Brackets |
152 |
| MUCO 3 Useless Code Useless Brackets |
235 |
| QAFM 3 Var Assignment from potentially many rows |
70 |
| QAFM 3 Var Assignment from potentially many rows |
85 |
| QAFM 3 Var Assignment from potentially many rows |
93 |
| QCRS 3 Conditional Result Set |
127 |
| QCTC 3 Conditional Table Creation |
110 |
| QCTC 3 Conditional Table Creation |
151 |
| QCTC 3 Conditional Table Creation |
193 |
| QCTC 3 Conditional Table Creation |
215 |
| QISO 3 Set isolation level |
31 |
| QNAJ 3 Not using ANSI Inner Join |
113 |
| QNAJ 3 Not using ANSI Inner Join |
155 |
| QNAJ 3 Not using ANSI Inner Join |
184 |
| QNAJ 3 Not using ANSI Inner Join |
196 |
| QNAJ 3 Not using ANSI Inner Join |
206 |
| QNAJ 3 Not using ANSI Inner Join |
217 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid} |
99 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid} |
114 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid} |
156 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid} |
166 |
 MRST 2 Result Set Marker |
127 |
| MRST 2 Result Set Marker |
228 |
| MSUB 2 Subquery Marker |
48 |
| MSUB 2 Subquery Marker |
183 |
| MSUB 2 Subquery Marker |
205 |
| MTR1 2 Metrics: Comments Ratio Comments: 31% |
11 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 26dec - 3exi + 2 |
11 |
| MTR3 2 Metrics: Query Complexity Complexity: 122 |
11 |
| PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 |
110 |
| PRED_QUERY_COLLECTION 2 {d=master..sysdevices, u=master..sysusages} 0 |
151 |
| PRED_QUERY_COLLECTION 2 {p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0 |
183 |
| PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, p=sybsystemprocs..syspartitions, s=sybsystemprocs..syssegments} 0 |
193 |
| PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, s=sybsystemprocs..syssegments} 0 |
205 |
| PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, s=sybsystemprocs..syssegments} 0 |
215 |
