Database	Proc	Application	Created	Links
sybsystemprocs	sp_tempdb		31 Aug 14	Defects Dependencies

1     
2     
3     /*
4     ** Messages for "sp_tempdb"
5     **
6     ** 17240, "'%1!' is not a valid name."
7     ** 17260, "Can't run %1! from within a transaction."
8     ** 18954, "Usage:"
9     ** 18955, "sp_tempdb 'help' "
10    ** 18956, "sp_tempdb 'create', "
11    ** 18957, "sp_tempdb 'drop', "
12    ** 18958, "sp_tempdb 'add', , "
13    ** 18959, "sp_tempdb 'remove', , "
14    ** 18960, "sp_tempdb 'bind', , , , [, [, ]]"
15    ** 18961, "sp_tempdb 'unbind', , [, ]"  [for SMP]
16    ** 19588, "sp_tempdb 'unbind', , [, [, ]]" [For SDC]
17    ** 18971, "sp_tempdb 'unbindall_db', "
18    ** 19583, "sp_tempdb 'unbindall_gr', "
19    ** 18962, "sp_tempdb 'show', , "
20    ** 18963, " = ['LG' ('login_name') | 'AP' ('application_name')];  = ['GR' ('group') | 'DB' ('database')] "
21    ** 18973, " = ['hard' | 'soft']"
22    ** 18964, " = ['all' | 'gr' ('groups') | 'db' ('databases') | 'login' ('logins') | 'app' ('applications') | 'who']"
23    ** 18965, "sp_tempdb : Unrecognized command %1!. execute 'sp_tempdb help' for usage."
24    ** 18970, "sp_tempdb 'who', "
25    ** 19875  "Parameter '%1!' is too long. Maximum length is '%2!'."
26    */
27    
28    /*
29    **      Procedure sp_tempdb
30    **
31    **      This procedure provides the administration interface for
32    **	managing temporary database groups, and user defined bindings.
33    **	
34    **	This procedure does its work by calling other stored procedures
35    **	based on the command passed in.
36    **      
37    */
38    
39    create procedure sp_tempdb
40        @cmd varchar(30) = null, /* the operation to perform */
41        @arg1 varchar(255) = null, /* first argument */
42        @arg2 varchar(265) = null, /* second argument */
43        @arg3 varchar(255) = null, /* third argument */
44        @arg4 varchar(255) = null, /* fourth argument */
45        @arg5 varchar(255) = null, /* fifth argument   */
46        @arg6 varchar(255) = null /* sixth argument  */
47    as
48    
49    
50        declare @retstat int /* return status */
51        declare @msg varchar(250) /* buffer for message */
52        declare @arg1_temp varchar(255) /* temp. holder of arg1 */
53        declare @svrmode int /* Indicates the SMP or SDC mode */
54        declare @SMP int /* Indicates SMP Server */
55        declare @SDC int /* Indicates SDC Server */
56    
57        select @SMP = 0,
58            @SDC = 1
59    
60        if @@clustermode != "shared disk cluster"
61        begin
62            select @svrmode = @SMP
63        end
64        else
65        begin
66            select @svrmode = @SDC
67        end
68    
69        /*
70        **  If we're in a transaction, disallow this since it might make recovery
71        **  impossible.
72        */
73        if @@trancount > 0
74        begin
75            raiserror 17260, "sp_tempdb"
76            return (1)
77    
78        end
79        begin
80            set chained off
81        end
82        set transaction isolation level 1
83    
84        /*
85        ** Check if user has sa role. Procedure can only be executed 
86        ** if user has sa_role. proc_role will print any error message.
87        */
88        if (proc_role("sa_role") = 0)
89            return 1
90    
91        /*
92        ** Print usage info or "help" or in absense of a command 
93        */
94        if (@cmd = "help") or (@cmd is NULL)
95        begin
96            exec sp_getmessage 18954, @msg output
97            print @msg
98            exec sp_getmessage 18955, @msg output
99            print @msg
100           exec sp_getmessage 18956, @msg output
101           print @msg
102           exec sp_getmessage 18957, @msg output
103           print @msg
104           exec sp_getmessage 18958, @msg output
105           print @msg
106           exec sp_getmessage 18959, @msg output
107           print @msg
108           exec sp_getmessage 18960, @msg output
109           print @msg
110           if (@svrmode = @SMP)
111           begin
112               exec sp_getmessage 18961, @msg output
113               print @msg
114           end
115           else if (@svrmode = @SDC)
116           begin
117               exec sp_getmessage 19588, @msg output
118               print @msg
119           end
120           exec sp_getmessage 18971, @msg output
121           print @msg
122           exec sp_getmessage 19583, @msg output
123           print @msg
124           exec sp_getmessage 18962, @msg output
125           print @msg
126           exec sp_getmessage 18970, @msg output
127           print @msg
128           print " "
129           exec sp_getmessage 18963, @msg output
130           print @msg
131           exec sp_getmessage 18973, @msg output
132           print @msg
133           exec sp_getmessage 18964, @msg output
134           print @msg
135           return (0)
136       end
137       /*
138       ** Command is to create a new group 
139       */
140       else if (@cmd = "create")
141       begin
142           /*
143           ** Check for incorrect number of arguments
144           */
145           if ((@arg1 is NULL) OR (@arg2 is not NULL) OR (@arg3 is not NULL)
146                   OR (@arg4 is not NULL) OR (@arg5 is not NULL) OR (@arg6 is not NULL))
147           begin
148   
149               exec sp_getmessage 18954, @msg output
150               print @msg
151               exec sp_getmessage 18956, @msg output
152               print @msg
153               return 1
154           end
155           else
156           /*
157           ** execute worker stored procedure 
158           */
159           begin
160               exec @retstat = sp_multdb_creategroup @arg1
161               return @retstat
162           end
163       end
164       /*
165       ** Command is to delete a group  
166       */
167       else if (@cmd = "drop")
168       begin
169           /*
170           ** Check for incorrect number of arguments
171           */
172           if ((@arg1 is NULL) OR (@arg2 is not NULL) OR (@arg3 is not NULL)
173                   OR (@arg4 is not NULL) OR (@arg5 is not NULL) OR (@arg6 is not NULL))
174           begin
175               exec sp_getmessage 18954, @msg output
176               print @msg
177               exec sp_getmessage 18957, @msg output
178               print @msg
179               return 1
180           end
181           else
182           /*
183           ** execute worker stored procedure 
184           */
185           begin
186               exec @retstat = sp_multdb_dropgroup @arg1
187               return @retstat
188           end
189       end
190       /*
191       ** Command is to add a temporary database to a group  
192       */
193       else if (@cmd = "add")
194       begin
195           /*
196           ** Check for incorrect number of arguments
197           */
198           if ((@arg1 is NULL) OR (@arg2 is NULL) OR (@arg3 is not NULL)
199                   OR (@arg4 is not NULL) OR (@arg5 is not NULL) OR (@arg6 is not NULL))
200           begin
201               exec sp_getmessage 18954, @msg output
202               print @msg
203               exec sp_getmessage 18958, @msg output
204               print @msg
205               return 1
206           end
207           else
208           /*
209           ** execute worker stored procedure. Invalid names
210           ** for @arg1 and @arg2 are caught in sp_multdb_addtogroup.
211           */
212           begin
213               exec @retstat = sp_multdb_addtogroup @arg1, @arg2
214               return @retstat
215           end
216       end
217       /*
218       ** Command is to remove a temporary database from a group  
219       */
220       else if (@cmd = "remove")
221       begin
222           /*
223           ** Check for incorrect number of arguments
224           */
225           if ((@arg1 is NULL) OR (@arg2 is NULL) OR (@arg3 is not NULL)
226                   OR (@arg4 is not NULL) OR (@arg5 is not NULL) OR (@arg6 is not NULL))
227           begin
228               exec sp_getmessage 18954, @msg output
229               print @msg
230               exec sp_getmessage 18959, @msg output
231               print @msg
232               return 1
233           end
234           else
235           /*
236           ** execute worker stored procedure. Invalid names
237           ** for @arg1 and @arg2 are caught in sp_multdb_removefromgroup.
238           */
239           begin
240               exec @retstat = sp_multdb_removefromgroup @arg1, @arg2
241               return @retstat
242           end
243       end
244       /*
245       ** Command is to create a new binding
246       */
247       else if (@cmd = "bind")
248       begin
249           /*
250           ** Check for incorrect number of arguments
251           */
252           if ((@arg1 is NULL) OR (@arg2 is NULL)
253                   OR (@arg3 is NULL) OR (@arg4 is NULL))
254           begin
255               exec sp_getmessage 18954, @msg output
256               print @msg
257               exec sp_getmessage 18960, @msg output
258               print @msg
259               return 1
260           end
261           else if (@arg2 = "")
262           /*
263           ** Not a valid name (login/application)
264           */
265           begin
266               /*
267               ** 17240, "'%1!' is not a valid name."
268               */
269               raiserror 17240, @arg2
270               return 1
271           end
272           else if (datalength(@arg2) > 255)
273           begin
274               raiserror 19875, @arg2, 255
275               return 1
276           end
277           /*
278           ** execute worker stored procedure 
279           */
280           begin
281               exec @retstat = sp_multdb_bind @arg2, @arg1, @arg5, @arg3, @arg4, @arg6
282               return @retstat
283           end
284       end
285       /*
286       ** Command is to remove a binding 
287       */
288       else if (@cmd = "unbind")
289       begin
290           /*
291           ** Check for incorrect number of arguments
292           ** SMP: sp_tempdb unbind   [,  ]
293           ** SDC: sp_tempdb unbind   [, , ]
294           */
295           if ((@arg1 is NULL) OR (@arg2 is NULL) OR
296                   ((@svrmode = @SMP) and (@arg4 is not NULL)) OR
297                   ((@svrmode = @SDC) and (@arg5 is not NULL)))
298           begin
299               exec sp_getmessage 18954, @msg output
300               print @msg
301               if (@svrmode = @SMP)
302               begin
303                   exec sp_getmessage 18961, @msg output
304                   print @msg
305               end
306               else if (@svrmode = @SDC)
307               begin
308                   exec sp_getmessage 19588, @msg output
309                   print @msg
310               end
311               return 1
312           end
313           else
314           /*
315           ** execute worker stored procedure 
316           */
317           begin
318               if (@svrmode = @SMP)
319               begin
320                   exec @retstat = sp_multdb_unbind @arg2, @arg1, @arg3
321               end
322               else if (@svrmode = @SDC)
323               begin
324                   exec @retstat = sp_multdb_unbind @arg2, @arg1, @arg3, @arg4
325               end
326               return @retstat
327           end
328       end
329       /*
330       ** Command is to display information stored in sysattributes for class 16
331       */
332       else if (@cmd = "show")
333       begin
334           select @arg1_temp = @arg1
335   
336           /*
337           ** Get our short form for the argument.
338           */
339           if (@arg1_temp = "groups")
340           begin
341               select @arg1 = "gr"
342           end
343           else if (@arg1_temp = "databases")
344           begin
345               select @arg1 = "db"
346           end
347           else if (@arg1_temp = "logins")
348           begin
349               select @arg1 = "login"
350           end
351           else if (@arg1_temp = "applications")
352           begin
353               select @arg1 = "app"
354           end
355   
356   
357           /* Check for invalid arguments */
358           if (@arg1 not in ("all", "gr", "db", "login", "app", "who", NULL))
359           begin
360               exec sp_getmessage 18954, @msg output
361               print @msg
362               exec sp_getmessage 18962, @msg output
363               print @msg
364               return 1
365           end
366   
367           /* Always need a dbname with "who" */
368           if (@arg1 = "who") and (@arg2 is NULL)
369           begin
370               exec sp_getmessage 18954, @msg output
371               print @msg
372               exec sp_getmessage 18966, @msg output
373               print @msg
374               return 1
375           end
376   
377           /*
378           ** execute worker stored procedure 
379           */
380           exec @retstat = sp_multdb_show @arg1, @arg2
381           return @retstat
382       end
383       /*
384       ** User wants to see active sessions bound to a temp. database 
385       */
386       else if (@cmd = "who")
387       begin
388           if (@arg1 is NULL)
389           begin
390               exec sp_getmessage 18954, @msg output
391               print @msg
392               exec sp_getmessage 18970, @msg output
393               print @msg
394               return 1
395           end
396   
397           /*
398           ** execute worker stored procedure
399           */
400           exec @retstat = sp_multdb_show @cmd, @arg1
401           return @retstat
402       end
403       /*
404       ** User wants to drop all login/app bindings for a temporary database.
405       */
406       else if (@cmd = "unbindall_db")
407       begin
408           if (@arg1 is NULL)
409           begin
410               exec sp_getmessage 18954, @msg output
411               print @msg
412               exec sp_getmessage 18971, @msg output
413               print @msg
414               return 1
415           end
416   
417           /*
418           ** execute worker stored procedure
419           */
420           exec @retstat = sp_multdb_unbindall_db @arg1
421           return @retstat
422       end
423       /*
424       ** User wants to drop all login/app bindings for a temporary database group.
425       */
426       else if (@cmd = "unbindall_gr")
427       begin
428           if (@arg1 is NULL)
429           begin
430               exec sp_getmessage 18954, @msg output
431               print @msg
432               exec sp_getmessage 19583, @msg output
433               print @msg
434               return 1
435           end
436   
437           /*
438           ** execute worker stored procedure
439           */
440           exec @retstat = sp_multdb_unbindall_gr @arg1
441           return @retstat
442       end
443       else
444       /*
445       ** Unrecognized command 
446       */
447       begin
448           exec sp_getmessage 18965, @msg output
449           print @msg, @cmd
450           return 1
451       end
452   
453       return (0)
454

DEFECTS
MURC 6 Unreachable Code	453
MEST 4 Empty String will be replaced by Single Space	261
MTYP 4 Assignment type mismatch @tdbgroup: varchar(255) = varchar(265)	213
MTYP 4 Assignment type mismatch @tdbgroup: varchar(255) = varchar(265)	240
MTYP 4 Assignment type mismatch @hardness: varchar(30) = varchar(255)	281
MTYP 4 Assignment type mismatch @obj_name: varchar(255) = varchar(265)	281
MTYP 4 Assignment type mismatch @obj_type: varchar(30) = varchar(255)	281
MTYP 4 Assignment type mismatch @tdb_type: varchar(30) = varchar(255)	281
MTYP 4 Assignment type mismatch @obj_name: varchar(255) = varchar(265)	320
MTYP 4 Assignment type mismatch @obj_type: varchar(30) = varchar(255)	320
MTYP 4 Assignment type mismatch @obj_name: varchar(255) = varchar(265)	324
MTYP 4 Assignment type mismatch @obj_type: varchar(30) = varchar(255)	324
MTYP 4 Assignment type mismatch @name: varchar(255) = varchar(265)	380
MTYP 4 Assignment type mismatch @option: varchar(10) = varchar(255)	380
MTYP 4 Assignment type mismatch @option: varchar(10) = varchar(30)	400
MNER 3 No Error Check should check return value of exec	96
MNER 3 No Error Check should check return value of exec	98
MNER 3 No Error Check should check return value of exec	100
MNER 3 No Error Check should check return value of exec	102
MNER 3 No Error Check should check return value of exec	104
MNER 3 No Error Check should check return value of exec	106
MNER 3 No Error Check should check return value of exec	108
MNER 3 No Error Check should check return value of exec	112
MNER 3 No Error Check should check return value of exec	117
MNER 3 No Error Check should check return value of exec	120
MNER 3 No Error Check should check return value of exec	122
MNER 3 No Error Check should check return value of exec	124
MNER 3 No Error Check should check return value of exec	126
MNER 3 No Error Check should check return value of exec	129
MNER 3 No Error Check should check return value of exec	131
MNER 3 No Error Check should check return value of exec	133
MNER 3 No Error Check should check return value of exec	149
MNER 3 No Error Check should check return value of exec	151
MNER 3 No Error Check should check return value of exec	160
MNER 3 No Error Check should check return value of exec	175
MNER 3 No Error Check should check return value of exec	177
MNER 3 No Error Check should check return value of exec	186
MNER 3 No Error Check should check return value of exec	201
MNER 3 No Error Check should check return value of exec	203
MNER 3 No Error Check should check return value of exec	213
MNER 3 No Error Check should check return value of exec	228
MNER 3 No Error Check should check return value of exec	230
MNER 3 No Error Check should check return value of exec	240
MNER 3 No Error Check should check return value of exec	255
MNER 3 No Error Check should check return value of exec	257
MNER 3 No Error Check should check return value of exec	281
MNER 3 No Error Check should check return value of exec	299
MNER 3 No Error Check should check return value of exec	303
MNER 3 No Error Check should check return value of exec	308
MNER 3 No Error Check should check return value of exec	320
MNER 3 No Error Check should check return value of exec	324
MNER 3 No Error Check should check return value of exec	360
MNER 3 No Error Check should check return value of exec	362
MNER 3 No Error Check should check return value of exec	370
MNER 3 No Error Check should check return value of exec	372
MNER 3 No Error Check should check return value of exec	380
MNER 3 No Error Check should check return value of exec	390
MNER 3 No Error Check should check return value of exec	392
MNER 3 No Error Check should check return value of exec	400
MNER 3 No Error Check should check return value of exec	410
MNER 3 No Error Check should check return value of exec	412
MNER 3 No Error Check should check return value of exec	420
MNER 3 No Error Check should check return value of exec	430
MNER 3 No Error Check should check return value of exec	432
MNER 3 No Error Check should check return value of exec	440
MNER 3 No Error Check should check return value of exec	448
MUCO 3 Useless Code Useless Brackets	76
MUCO 3 Useless Code Useless Begin-End Pair	79
MUCO 3 Useless Code Useless Brackets	88
MUCO 3 Useless Code Useless Brackets	110
MUCO 3 Useless Code Useless Brackets	115
MUCO 3 Useless Code Useless Brackets	135
MUCO 3 Useless Code Useless Brackets	140
MUCO 3 Useless Code Useless Brackets	145
MUCO 3 Useless Code Useless Brackets	167
MUCO 3 Useless Code Useless Brackets	172
MUCO 3 Useless Code Useless Brackets	193
MUCO 3 Useless Code Useless Brackets	198
MUCO 3 Useless Code Useless Brackets	220
MUCO 3 Useless Code Useless Brackets	225
MUCO 3 Useless Code Useless Brackets	247
MUCO 3 Useless Code Useless Brackets	252
MUCO 3 Useless Code Useless Brackets	261
MUCO 3 Useless Code Useless Brackets	272
MUCO 3 Useless Code Useless Brackets	288
MUCO 3 Useless Code Useless Brackets	295
MUCO 3 Useless Code Useless Brackets	301
MUCO 3 Useless Code Useless Brackets	306
MUCO 3 Useless Code Useless Brackets	318
MUCO 3 Useless Code Useless Brackets	322
MUCO 3 Useless Code Useless Brackets	332
MUCO 3 Useless Code Useless Brackets	339
MUCO 3 Useless Code Useless Brackets	343
MUCO 3 Useless Code Useless Brackets	347
MUCO 3 Useless Code Useless Brackets	351
MUCO 3 Useless Code Useless Brackets	358
MUCO 3 Useless Code Useless Brackets	386
MUCO 3 Useless Code Useless Brackets	388
MUCO 3 Useless Code Useless Brackets	406
MUCO 3 Useless Code Useless Brackets	408
MUCO 3 Useless Code Useless Brackets	426
MUCO 3 Useless Code Useless Brackets	428
MUCO 3 Useless Code Useless Brackets	453
QISO 3 Set isolation level	82
MTR1 2 Metrics: Comments Ratio Comments: 37%	39
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 42 = 67dec - 27exi + 2	39
MTR3 2 Metrics: Query Complexity Complexity: 244	39