DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpserver  31 Aug 14Defects Dependencies

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_helpserver"
6     **
7     ** 17270, "There is not a server named '%1!'."
8     ** 17680, "There are no remote servers defined."
9     ** 19958, "Backup Server policy is set to '%1!'."
10    */
11    create procedure sp_helpserver
12        @server varchar(255) = NULL /* server name */
13    as
14    
15        declare @msg varchar(1024)
16        declare @len1 int, @len2 int
17        declare @bs_policy varchar(255)
18    
19        begin
20    
21            if @@trancount = 0
22            begin
23                set chained off
24            end
25    
26            set transaction isolation level 1
27    
28            set nocount on
29    
30            /*
31            **  If no server name given, get 'em all.
32            */
33            if @server is null
34            begin
35                select @server = "%"
36            end
37    
38            if @server = "SYB_BACKUP"
39            begin
40                select @bs_policy = srvnetname from master..sysservers where srvname = "SYB_BACKUP"
41    
42                if ((lower(@bs_policy) = "$dedicated") or (lower(@bs_policy) = "$roundrobin"))
43                begin
44                    select @server = "%_BS"
45    
46                    /* 19958, "Backup Server policy is set to '%1!'." */
47                    exec sp_getmessage 19958, @msg output
48                    print @msg, @bs_policy
49                end
50            end
51    
52            /*
53            **  Does the server exist?
54            */
55            if not exists (select *
56                    from master.dbo.sysservers
57                    where srvname like @server
58                        or srvnetname like @server)
59            begin
60                if @server = "%"
61                begin
62                    /* 17680, "There are no remote servers defined." */
63                    exec sp_getmessage 17680, @msg output
64                    print @msg
65                end
66                else
67                    /* 17270, "There is not a server named '%1!'." */
68                    raiserror 17270, @server
69                return (1)
70            end
71    
72            /*
73            **  Display server information.
74            **  First put it into a temp table so we can examine the status 
75            **  bits and turn them into english.
76            */
77            create table #spt_server
78            (
79                name varchar(255),
80                network varchar(32),
81                secmech varchar(255) NULL,
82                principal varchar(255) NULL,
83                class varchar(15) NULL,
84                status varchar(255),
85                id smallint,
86                cost smallint NULL
87            )
88    
89            /*
90            **  Initialize #spt_server from sysservers.
91            */
92            insert into #spt_server
93            select s.srvname, s.srvnetname, s.srvsecmech, s.srvprincipal, v.name, "",
94                id = s.srvid, s.srvcost
95            from master.dbo.sysservers s, master.dbo.spt_values v
96            where (s.srvname like @server
97                    or s.srvnetname like @server)
98                and s.srvclass *= v.number
99                and v.type = 'X' and v.name != 'access_server'
100   
101           /* 
102           **  Now for each nonlocal server in #spt_server, build the server
103           **  status description.  Logic patterned after helpdb.
104           */
105   
106           declare @cursrvid smallint /* the current server we're working on. */
107           declare @srvdesc varchar(255) /* the server description string. */
108           declare @bitdesc varchar(30) /* the bit description for the server */
109   
110           /*
111           **  Set @cursrvid to the first server id.
112           */
113           select @cursrvid = min(id)
114           from #spt_server
115           where id != 0
116   
117           /*
118           **  Parse the status.
119           */
120           while @cursrvid is not NULL
121           begin
122               /*
123               **  Initialize @srvdesc.
124               */
125               select @srvdesc = ""
126   
127               /* 
128               **  Check timeout bit.  Note special kludgy handling
129               **  due to previous definition of timeout and no timeout
130               **  in spt_values.
131               */
132               select @bitdesc = null
133               select @bitdesc = v.name
134               from master.dbo.spt_values v
135               where v.type = "A"
136                   and v.number = 0
137   
138               if exists (select *
139                       from master.dbo.spt_values v, master.dbo.sysservers s
140                       where s.srvid = @cursrvid
141                           and s.srvstatus & v.number = 1)
142               begin
143                   select @srvdesc = "no " + @bitdesc
144               end
145               else
146               begin
147                   select @srvdesc = @bitdesc
148               end
149   
150               /* 
151               **  Check network password encryption status.
152               */
153               select @bitdesc = null
154               select @bitdesc = v.name
155               from master.dbo.spt_values v
156               where v.type = "A"
157                   and v.number = 2
158   
159               if exists (select *
160                       from master.dbo.spt_values v, master.dbo.sysservers s
161                       where s.srvid = @cursrvid
162                           and s.srvstatus & v.number = 2)
163               begin
164                   if @srvdesc != ""
165                       select @srvdesc = @srvdesc + ", " + @bitdesc
166                   else
167                       select @srvdesc = @bitdesc
168               end
169               else
170               begin
171                   if @srvdesc != ""
172                       select @srvdesc = @srvdesc + ", no " + @bitdesc
173                   else
174                       select @srvdesc = "no " + @bitdesc
175               end
176   
177               /*
178               ** if OMNI is configured, handle the readonly status
179               */
180               if exists (select * from master.dbo.sysconfigures cf,
181                           master.dbo.syscurconfigs cc
182                       where cf.config = cc.config
183                           and cc.value = 1
184                           and cf.name = 'enable cis')
185               begin
186                   select @bitdesc = null
187                   select @bitdesc = v.name
188                   from master.dbo.spt_values v
189                   where v.type = "A"
190                       and v.number = 4
191                   if exists (select *
192                           from master.dbo.spt_values v, master.dbo.sysservers s
193                           where s.srvid = @cursrvid
194                               and s.srvstatus & v.number = 4)
195                   begin
196                       if @srvdesc != ""
197                           select @srvdesc = @srvdesc + ", " + @bitdesc
198                       else
199                           select @srvdesc = @bitdesc
200                   end
201                   else
202                   begin
203                       if @srvdesc != ""
204                           select @srvdesc = @srvdesc + ", writable "
205                       else
206                           select @srvdesc = "writable "
207                   end
208               end
209   
210               /*
211               ** Further check security options namely, 
212               ** "use confidentiality", "use message integrity", or
213               ** "mutual authentication". Notice that these three options 
214               ** can co-exist for a given remote server.
215               */
216               select @bitdesc = null
217               select @bitdesc = v.name
218               from master.dbo.spt_values v
219               where v.type = "A"
220                   and v.number = 64
221   
222               if exists (select *
223                       from master.dbo.spt_values v, master.dbo.sysservers s
224                       where s.srvid = @cursrvid
225                           and s.srvstatus & v.number = 64)
226   
227               begin
228                   /*
229                   ** We are sure that @srvdesc is not empty at this point.
230                   */
231                   select @srvdesc = @srvdesc + ", " + @bitdesc
232               end
233   
234               select @bitdesc = null
235               select @bitdesc = v.name
236               from master.dbo.spt_values v
237               where v.type = "A"
238                   and v.number = 128
239   
240               if exists (select *
241                       from master.dbo.spt_values v, master.dbo.sysservers s
242                       where s.srvid = @cursrvid
243                           and s.srvstatus & v.number = 128)
244   
245               begin
246                   /*
247                   ** We are sure that @srvdesc is not empty at this point.
248                   */
249                   select @srvdesc = @srvdesc + ", " + @bitdesc
250               end
251   
252               select @bitdesc = null
253               select @bitdesc = v.name
254               from master.dbo.spt_values v
255               where v.type = "A"
256                   and v.number = 256
257   
258               if exists (select *
259                       from master.dbo.spt_values v, master.dbo.sysservers s
260                       where s.srvid = @cursrvid
261                           and s.srvstatus & v.number = 256)
262   
263               begin
264                   /*
265                   ** We are sure that @srvdesc is not empty at this point.
266                   */
267                   select @srvdesc = @srvdesc + ", " + @bitdesc
268               end
269   
270               /*
271               ** Check "cis hafailover".
272               */
273               select @bitdesc = null
274               select @bitdesc = v.name
275               from master.dbo.spt_values v
276               where v.type = "A"
277                   and v.number = 32
278   
279               if exists (select *
280                       from master.dbo.spt_values v, master.dbo.sysservers s
281                       where s.srvid = @cursrvid
282                           and s.srvstatus & v.number = 32)
283               begin
284                   if @srvdesc != ""
285                       select @srvdesc = @srvdesc + ", " + @bitdesc
286                   else
287                       select @srvdesc = @bitdesc
288               end
289   
290               /*
291               ** Check for 'server logins'
292               */
293               select @bitdesc = null
294               select @bitdesc = v.name
295               from master.dbo.spt_values v
296               where v.type = "A"
297                   and v.number = 512
298   
299               if exists (select *
300                       from master.dbo.spt_values v, master.dbo.sysservers s
301                       where s.srvid = @cursrvid
302                           and s.srvstatus & v.number = 512)
303               begin
304                   if @srvdesc != ""
305                       select @srvdesc = @srvdesc + ", " + @bitdesc
306                   else
307                       select @srvdesc = @bitdesc
308               end
309   
310               /*
311               ** Check for 'external engine auto start', this option is for the
312               ** EJB Class of servers only hence when reporting the various servers
313               ** for the ejb server only this option should be shown in the status
314               */
315               select @bitdesc = null
316               select @bitdesc = v.name
317               from master.dbo.spt_values v
318               where v.type = "A"
319                   and v.number = 1024
320   
321               if exists (select *
322                       from master.dbo.spt_values v, master.dbo.sysservers s
323                       where s.srvid = @cursrvid
324                           and s.srvstatus & v.number = 1024)
325               begin
326                   select @srvdesc = @bitdesc
327               end
328               else
329               begin
330                   if exists (select * from master.dbo.sysservers s
331                           where s.srvid = @cursrvid and s.srvclass = 10)
332                   begin
333                       select @srvdesc = ""
334                   end
335               end
336   
337               /* 
338               ** Check for 'negotiated logins'
339               */
340               select @bitdesc = null
341               select @bitdesc = v.name from master.dbo.spt_values v
342               where v.type = 'A' and v.number = 4096
343               if exists (select * from master.dbo.spt_values v, master.dbo.sysservers s
344                       where s.srvid = @cursrvid and s.srvstatus & v.number = 4096)
345               begin
346                   if @srvdesc != ""
347                       select @srvdesc = @srvdesc + ", " + @bitdesc
348                   else
349                       select @srvdesc = @bitdesc
350               end
351   
352               /* 
353               ** Check for 'relocated joins'
354               */
355               select @bitdesc = null
356               select @bitdesc = v.name from master.dbo.spt_values v
357               where v.type = 'A2' and v.number = 1
358               if exists (select * from master.dbo.spt_values v, master.dbo.sysservers s
359                       where s.srvid = @cursrvid and s.srvstatus2 & v.number = 1)
360               begin
361                   if @srvdesc != ""
362                       select @srvdesc = @srvdesc + ", " + @bitdesc
363                   else
364                       select @srvdesc = @bitdesc
365               end
366   
367               /* 
368               ** Check for 'incompatible sort order'
369               */
370               select @bitdesc = null
371               select @bitdesc = v.name from master.dbo.spt_values v
372               where v.type = 'A2' and v.number = 8
373               if exists (select * from master.dbo.spt_values v, master.dbo.sysservers s
374                       where s.srvid = @cursrvid and s.srvstatus2 & v.number = 8)
375               begin
376                   if @srvdesc != ""
377                       select @srvdesc = @srvdesc + ", " + @bitdesc
378                   else
379                       select @srvdesc = @bitdesc
380               end
381   
382               /*
383               ** Check for 'enable login redirection'
384               */
385               select @bitdesc = null
386               select @bitdesc = v.name from master.dbo.spt_values v
387               where v.type = 'A2' and v.number = 2
388               if exists (select * from master.dbo.spt_values v, master.dbo.sysservers s
389                       where s.srvid = @cursrvid and s.srvstatus2 & v.number = 2)
390               begin
391                   if @srvdesc != ""
392                       select @srvdesc = @srvdesc + ", " + @bitdesc
393                   else
394                       select @srvdesc = @bitdesc
395               end
396               /*
397               ** Check for 'cluster instance'
398               */
399               select @bitdesc = null
400               select @bitdesc = v.name from master.dbo.spt_values v
401               where v.type = 'A2' and v.number = 4
402               if exists (select * from master.dbo.spt_values v, master.dbo.sysservers s
403                       where s.srvid = @cursrvid and s.srvstatus2 & v.number = 4)
404               begin
405                   if @srvdesc != ""
406                       select @srvdesc = @srvdesc + ", " + @bitdesc
407                   else
408                       select @srvdesc = @bitdesc
409               end
410   
411               /*
412               **  Save the description.
413               */
414               update #spt_server
415               set status = @srvdesc
416               from #spt_server
417               where id = @cursrvid
418   
419               /*
420               **  Now get the next, if any, server id.
421               */
422               select @cursrvid = min(id)
423               from #spt_server
424               where id > @cursrvid
425           end
426   
427           /*
428           **  Display the information we got.
429           */
430           if exists (select * from master.dbo.sysconfigures cf,
431                       master.dbo.syscurconfigs cc
432                   where cf.config = cc.config
433                       and cc.value = 1
434                       and cf.name = 'enable cis')
435           begin
436               /*
437               ** OMNI is configured, display the server class and cost as well
438               */
439   
440               exec sp_autoformat @fulltabname = #spt_server,
441                   @selectlist = " name, 'network_name' = network, 'security_mechanism' = secmech, 'server_principal' = principal, class, status,id, cost",
442                   @orderby = "order by name"
443           end
444           else
445           begin
446   
447               exec sp_autoformat @fulltabname = #spt_server,
448                   @selectlist = " name, 'network_name' = network, 'security_mechanism' = secmech, status,id",
449                   @orderby = "order by name"
450           end
451   
452           return (0)
453       end
454   


exec sp_procxmode 'sp_helpserver', 'AnyMode'
go

Grant Execute on sp_helpserver to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 98
 QJWI 5 Join or Sarg Without Index 141
 QJWI 5 Join or Sarg Without Index 162
 QJWI 5 Join or Sarg Without Index 194
 QJWI 5 Join or Sarg Without Index 225
 QJWI 5 Join or Sarg Without Index 243
 QJWI 5 Join or Sarg Without Index 261
 QJWI 5 Join or Sarg Without Index 282
 QJWI 5 Join or Sarg Without Index 302
 QJWI 5 Join or Sarg Without Index 324
 QJWI 5 Join or Sarg Without Index 344
 QJWI 5 Join or Sarg Without Index 359
 QJWI 5 Join or Sarg Without Index 374
 QJWI 5 Join or Sarg Without Index 389
 QJWI 5 Join or Sarg Without Index 403
 MEST 4 Empty String will be replaced by Single Space 93
 MEST 4 Empty String will be replaced by Single Space 125
 MEST 4 Empty String will be replaced by Single Space 164
 MEST 4 Empty String will be replaced by Single Space 171
 MEST 4 Empty String will be replaced by Single Space 196
 MEST 4 Empty String will be replaced by Single Space 203
 MEST 4 Empty String will be replaced by Single Space 284
 MEST 4 Empty String will be replaced by Single Space 304
 MEST 4 Empty String will be replaced by Single Space 333
 MEST 4 Empty String will be replaced by Single Space 346
 MEST 4 Empty String will be replaced by Single Space 361
 MEST 4 Empty String will be replaced by Single Space 376
 MEST 4 Empty String will be replaced by Single Space 391
 MEST 4 Empty String will be replaced by Single Space 405
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch class: varchar(15) = varchar(28) 93
 MTYP 4 Assignment type mismatch network: varchar(32) = varchar(255) 93
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 440
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 447
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
99
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 98
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 115
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 331
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 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..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_helpserver  
 MNER 3 No Error Check should check return value of exec 47
 MNER 3 No Error Check should check return value of exec 63
 MNER 3 No Error Check should check @@error after insert 92
 MNER 3 No Error Check should check @@error after update 414
 MNER 3 No Error Check should check return value of exec 440
 MNER 3 No Error Check should check return value of exec 447
 MUCO 3 Useless Code Useless Begin-End Pair 19
 MUCO 3 Useless Code Useless Brackets 42
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 452
 MUIN 3 Column created using implicit nullability 77
 MUUF 3 Update or Delete with Useless From Clause 414
 QAFM 3 Var Assignment from potentially many rows 133
 QAFM 3 Var Assignment from potentially many rows 154
 QAFM 3 Var Assignment from potentially many rows 187
 QAFM 3 Var Assignment from potentially many rows 217
 QAFM 3 Var Assignment from potentially many rows 235
 QAFM 3 Var Assignment from potentially many rows 253
 QAFM 3 Var Assignment from potentially many rows 274
 QAFM 3 Var Assignment from potentially many rows 294
 QAFM 3 Var Assignment from potentially many rows 316
 QAFM 3 Var Assignment from potentially many rows 341
 QAFM 3 Var Assignment from potentially many rows 356
 QAFM 3 Var Assignment from potentially many rows 371
 QAFM 3 Var Assignment from potentially many rows 386
 QAFM 3 Var Assignment from potentially many rows 400
 QISO 3 Set isolation level 26
 QNAJ 3 Not using ANSI Inner Join 139
 QNAJ 3 Not using ANSI Inner Join 160
 QNAJ 3 Not using ANSI Inner Join 180
 QNAJ 3 Not using ANSI Inner Join 192
 QNAJ 3 Not using ANSI Inner Join 223
 QNAJ 3 Not using ANSI Inner Join 241
 QNAJ 3 Not using ANSI Inner Join 259
 QNAJ 3 Not using ANSI Inner Join 280
 QNAJ 3 Not using ANSI Inner Join 300
 QNAJ 3 Not using ANSI Inner Join 322
 QNAJ 3 Not using ANSI Inner Join 343
 QNAJ 3 Not using ANSI Inner Join 358
 QNAJ 3 Not using ANSI Inner Join 373
 QNAJ 3 Not using ANSI Inner Join 388
 QNAJ 3 Not using ANSI Inner Join 402
 QNAJ 3 Not using ANSI Inner Join 430
 QNAO 3 Not using ANSI Outer Join 95
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
184
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
434
 QTLO 3 Top-Level OR 57
 VUNU 3 Variable is not used @len1 16
 VUNU 3 Variable is not used @len2 16
 MSUB 2 Subquery Marker 55
 MSUB 2 Subquery Marker 138
 MSUB 2 Subquery Marker 159
 MSUB 2 Subquery Marker 180
 MSUB 2 Subquery Marker 191
 MSUB 2 Subquery Marker 222
 MSUB 2 Subquery Marker 240
 MSUB 2 Subquery Marker 258
 MSUB 2 Subquery Marker 279
 MSUB 2 Subquery Marker 299
 MSUB 2 Subquery Marker 321
 MSUB 2 Subquery Marker 330
 MSUB 2 Subquery Marker 343
 MSUB 2 Subquery Marker 358
 MSUB 2 Subquery Marker 373
 MSUB 2 Subquery Marker 388
 MSUB 2 Subquery Marker 402
 MSUB 2 Subquery Marker 430
 MTR1 2 Metrics: Comments Ratio Comments: 21% 11
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 70 = 70dec - 2exi + 2 11
 MTR3 2 Metrics: Query Complexity Complexity: 288 11
 PRED_QUERY_COLLECTION 2 {c=master..sysconfigures, c2=master..syscurconfigs} 0 180
 PRED_QUERY_COLLECTION 2 {c=master..sysconfigures, c2=master..syscurconfigs} 0 430

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
calls proc sybsystemprocs..sp_autoformat  
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..syscolumns (1)  
   calls proc sybsystemprocs..sp_namecrack  
   reads table master..systypes (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
reads table master..spt_values (1)  
reads table master..sysservers (1)  
reads table master..sysconfigures (1)  
read_writes table tempdb..#spt_server (1) 
reads table master..syscurconfigs (1)