DatabaseProcApplicationCreatedLinks
sybsystemprocssp_multdb_unbindall_db  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** Messages for "sp_multdb_unbindall_db"
5     **
6     ** 17260, "Can't run %1! from within a transaction."
7     ** 18946, "Either the database '%1!' does not exist or is not a user 
8     **         created temporary database."
9     ** 19580, "Either the database '%1!' does not exist or is not a local 
10    **	   user temporary database." 
11    */
12    
13    /*
14    ** Procedure sp_multdb_unbindall_db
15    **
16    ** This procedure removes all login/application bindings from SYSATTRIBUTES for
17    ** a given database.
18    */
19    create or replace procedure sp_multdb_unbindall_db
20        @tempdb_name varchar(255) /* temporary database  */
21    as
22    
23        declare @class_id smallint, /* class in SYSATTRIBUTES */
24            @attrib_id smallint, /* attribute in SYSATTRIBUTES */
25            @int_val int, /* int_value in SYSATTRIBUTES */
26            @MULTDB_BINDDB int, /* binding is to a temporary database */
27            @svrmode int, /* Indicates the SMP or SDC mode */
28            @SMP int, /* Indicates SMP Server */
29            @SDC int, /* Indicates SDC Server */
30            @DBT3_USER_TEMPDB int, /* (SMP only) Bit value corresponding 
31            ** to DBT3_USER_TEMPDB 
32            */
33            @DBT3_LOCAL_USER_TEMPDB int, /* (SDC only) Bit value corresponding to
34            ** DBT3_LOCAL_USER_TEMPDB 
35            */
36            @action int, /* action for built in */
37            @ATTR_DROP int /* indicates a binding deletion */
38    
39        select @SMP = 0,
40            @SDC = 1
41    
42        if @@clustermode != "shared disk cluster"
43        begin
44            select @svrmode = @SMP
45    
46            select @DBT3_USER_TEMPDB = number
47            from master.dbo.spt_values
48            where type = "D3" and name = "user created temp db"
49        end
50        else
51        begin
52            select @svrmode = @SDC
53    
54            select @DBT3_LOCAL_USER_TEMPDB = number
55            from master.dbo.spt_values
56            where type = "D3" and name = "local user temp db"
57        end
58    
59        /*
60        **  If we're in a transaction, disallow this since it might make recovery
61        **  impossible.
62        */
63        if @@trancount > 0
64        begin
65            /*
66            ** 17260, "Can't run %1! from within a transaction."
67            */
68            raiserror 17260, "sp_multdb_unbindall_db"
69            return (1)
70        end
71        else
72        begin
73            /* Use TSQL mode of unchained transactions */
74            set chained off
75        end
76    
77        /* Don't do "Dirty Reads" */
78        set transaction isolation level 1
79    
80        select @class_id = 16 /* class is MULTEMPDB_CLASS */
81        select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */
82    
83        /*
84        ** Following constants must be consistent with their definition in
85        ** multempdb.c
86        */
87        select @MULTDB_BINDDB = 0
88    
89        /* keep in sync with sysattr.h */
90        select @ATTR_DROP = 3
91    
92        /*
93        ** In SMP, verify that specified dbname is a user created temporary database.
94        ** Database bindings for a login/application could be created with user 
95        ** create temporary databases only. Note that login/application binding 
96        ** cannot be created with system tempdb.
97        **
98        ** In SDC, verify that specified dbname is a local user temporary database.
99        ** Database bindings for a login/application could be created with local 
100       ** user temporary databases only.
101       */
102       if (@svrmode = @SDC)
103       begin
104           if not exists (select * from master..sysdatabases
105                   where name = @tempdb_name
106                       AND ((status3 & @DBT3_LOCAL_USER_TEMPDB) =
107                           @DBT3_LOCAL_USER_TEMPDB))
108           begin
109               /*
110               ** 19580, "Either the database '%1!' does not exist 
111               **         or is not a local user temporary database." 
112               */
113               raiserror 19580, @tempdb_name
114               return (1)
115           end
116       end
117       else /* @SMP */
118       begin
119           if not exists (select * from master..sysdatabases
120                   where name = @tempdb_name
121                       AND ((status3 & @DBT3_USER_TEMPDB) =
122                           @DBT3_USER_TEMPDB))
123           begin
124               /*
125               ** 18946, "Either the database '%1!' does not exist 
126               **	   or is not a user created temporary database."
127               */
128               raiserror 18946, @tempdb_name
129               return (1)
130           end
131       end
132   
133       /*
134       ** Delete all login and application bindings 
135       */
136       select @attrib_id = 1 /* Attribute is for binding */
137       select @int_val = @MULTDB_BINDDB /* Binding is to a database as 
138       ** opposed to a group 
139       */
140       select @action = @ATTR_DROP
141   
142       begin transaction multdb_unbindall_db
143   
144       /*
145       ** Delete the entry representing login/application bindings to the
146       ** given database.
147       */
148       delete from master..sysattributes
149       where class = @class_id
150           AND attribute = @attrib_id
151           AND char_value = @tempdb_name
152           AND int_value = @int_val
153   
154       if (@@error != 0)
155       begin
156           /*
157           ** 18608, "%1!: Delete row from master.dbo.sysattributes failed.
158           **	   Command aborted."
159           */
160           raiserror 18608, "sp_multdb_unbindall_db"
161           goto error_exit
162       end
163   
164       /*
165       ** Currently, no action is required upon notification of an unbindall event.
166       ** Uncomment the notification below when some action needs to be implemented.
167       ** Send only one notification for this operation. The notified routine needs
168       ** to decode the operation code as 'unbindall_db' by examining the received
169       ** arguments.
170       */
171       -- select @notify_status = 
172       --	attrib_notify (@class_id, @attrib_id, NULL, NULL,
173       --		NULL, NULL, NULL, NULL,
174       --		@MULTDB_BINDDB, @tempdb_name, NULL, NULL, "", @action)
175       --
176       --if (@notify_status = 0)
177       --begin
178       --	print "Internal error: Failed to remove binding."
179       --	goto error_exit
180       --end
181   
182       commit transaction multdb_unbindall_db
183       return (0)
184   
185   error_exit:
186       rollback transaction multdb_unbindall_db
187       return (1)
188   

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
48
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
56
 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..sysattributes  
 MGTP 3 Grant to public master..sysdatabases  
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 187
 QAFM 3 Var Assignment from potentially many rows 46
 QAFM 3 Var Assignment from potentially many rows 54
 QISO 3 Set isolation level 78
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {attribute, class}
149
 VNRD 3 Variable is not read @action 140
 MSUB 2 Subquery Marker 104
 MSUB 2 Subquery Marker 119
 MTR1 2 Metrics: Comments Ratio Comments: 56% 19
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 11dec - 4exi + 2 19
 MTR3 2 Metrics: Query Complexity Complexity: 60 19

DEPENDENCIES
PROCS AND TABLES USED
writes table master..sysattributes (1)  
reads table master..spt_values (1)  
reads table master..sysdatabases (1)  

CALLERS
called by proc sybsystemprocs..sp_tempdb