DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropdevice  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/dropdevice */
4     
5     /*
6     ** Messages for "sp_dropdevice"         17482
7     **
8     ** 17260, "Can't run %1! from within a transaction." 
9     ** 17471, "No such device exists -- run sp_helpdevice to list the DataServer devices." 
10    ** 17482, "Device is being used by a database. You can't drop it."
11    ** 17483, "Device dropped."
12    */
13    
14    create or replace procedure sp_dropdevice
15        @logicalname varchar(255) /* logical name of the device */
16    
17    as
18        declare @msg varchar(1024)
19        declare @len1 int, @len2 int
20        declare @status int
21        declare @status2 int
22        declare @factor int
23        declare @adbclass int
24        declare @dummy int
25        declare @nullarg char(1) /* Used to pass NULL as argument */
26        declare @gp_enabled int
27    
28    
29        select @adbclass = 28
30    
31    
32        /*
33        **  If we're in a transaction, disallow this since it might make recovery
34        **  impossible.
35        */
36        if @@trancount > 0
37        begin
38            /*
39            ** 17260, "Can't run %1! from within a transaction." 
40            */
41            raiserror 17260, "sp_dropdevice"
42            return (1)
43        end
44        else
45        begin
46            set chained off
47        end
48    
49        set transaction isolation level 1
50    
51        /* 
52        ** If granular permissions is not enabled check if user has sa role, 
53        ** proc_role will also do auditing if required. proc_role will also 
54        ** print error message if required.
55        ** If granular permissions is enabled check if user has 'manage disk' 
56        ** permission.
57        */
58        select @nullarg = NULL
59        execute @status = sp_aux_checkroleperm "sa_role", "manage disk",
60            @nullarg, @gp_enabled output
61    
62    
63        if (@gp_enabled = 0)
64        begin
65            if (proc_role("sa_role") = 0)
66            begin
67                return 1
68            end
69        end
70        else
71        begin
72            select @dummy = proc_auditperm("manage disk",
73                    @status)
74        end
75    
76        if (@status != 0)
77        begin
78            return (1)
79        end
80    
81        /*
82        **  Check and make sure that the device actually exists.
83        */
84        if not exists (select *
85                from master.dbo.sysdevices
86                where name = @logicalname)
87        begin
88            /*
89            ** 17471, "No such device exists -- run sp_helpdevice to list the DataServer devices." 
90            */
91            raiserror 17471
92            return (1)
93        end
94    
95        /*
96        **  Check and make sure that no database is using the device.
97        **  If so, print out who is using it and exit.
98        */
99        if exists (select *
100               from master.dbo.sysdatabases a, master.dbo.sysusages b,
101                   master.dbo.sysdevices c
102               where a.dbid = b.dbid
103                   and b.vdevno = c.vdevno
104                   and ((c.status & 6 != 0) or (c.status2 & 8 != 0))
105                   and c.name = @logicalname)
106       begin
107           /*
108           ** 17482, "Device is being used by a database. You can't drop it."
109           */
110           raiserror 17482
111   
112           select @factor = @@maxpagesize / @@pagesize
113   
114           select @len1 = max(datalength(a.name)),
115               @len2 = max(datalength(c.name))
116           from master.dbo.sysdatabases a, master.dbo.sysusages b,
117               master.dbo.sysdevices c
118           where a.dbid = b.dbid
119               and b.vdevno = c.vdevno
120               and ((c.status & 6 != 0) or (c.status2 & 8 != 0))
121               and c.name = @logicalname
122   
123           if (@len1 > 23 or @len2 > 21)
124               select database_name = a.name,
125                   device = c.name,
126                   size = convert(varchar(25), (size / 512) * @factor) + " MB"
127               from master.dbo.sysdatabases a, master.dbo.sysusages b,
128                   master.dbo.sysdevices c
129               where a.dbid = b.dbid
130                   and b.vdevno = c.vdevno
131                   and ((c.status & 6 != 0) or (c.status2 & 8 != 0))
132                   and c.name = @logicalname
133               order by a.name, c.name, size
134           else
135               select database_name = convert(char(23), a.name),
136                   device = convert(char(21), c.name),
137                   size = convert(varchar(25), (size / 512) * @factor) + " MB"
138               from master.dbo.sysdatabases a, master.dbo.sysusages b,
139                   master.dbo.sysdevices c
140               where a.dbid = b.dbid
141                   and b.vdevno = c.vdevno
142                   and ((c.status & 6 != 0) or (c.status2 & 8 != 0))
143                   and c.name = @logicalname
144               order by a.name, c.name, size
145   
146           return (1)
147       end
148       else if exists (select 1
149               from master..sysattributes a
150               where a.class = @adbclass
151                   and a.object_type = 'D'
152                   and a.attribute = 1
153                   and a.object_cinfo = @logicalname)
154       begin
155           /*
156           ** 17482, "Device is being used by a database. You can't drop it."
157           */
158           raiserror 17482
159   
160           select convert(char(30), name), convert(char(40), char_value)
161           from master.dbo.sysdatabases a, master.dbo.sysattributes b
162           where b.class = @adbclass
163               and b.object_type = 'D'
164               and b.attribute = 1
165               and b.object_cinfo = @logicalname
166               and b.object = a.dbid
167   
168           return (1)
169       end
170   
171       /*
172       **  Everything's ok so drop the device.
173       */
174       select @status = status from master.dbo.sysdevices
175       where name = @logicalname
176   
177       select @status2 = status2 from master.dbo.sysdevices
178       where name = @logicalname
179       /*
180       ** If database device or cache device (status2 & 8), close it and remove it 
181       ** from the sysdevices table using "disk release" command.  If dump device,
182       ** remove it from sysdevices table.
183       **/
184       if (((@status & 6) != 0) or ((@status2 & 8) != 0))
185           disk release name = @logicalname
186       else
187           delete master.dbo.sysdevices
188           where name = @logicalname
189       if (@@error != 0)
190           return (1)
191   
192       /*
193       ** 17483, "Device dropped."
194       */
195       exec sp_getmessage 17483, @msg output
196       print @msg
197   
198       return (0)
199   


exec sp_procxmode 'sp_dropdevice', 'AnyMode'
go

Grant Execute on sp_dropdevice to public
go
RESULT SETS
sp_dropdevice_rset_003
sp_dropdevice_rset_002
sp_dropdevice_rset_001

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 150
 QTYP 4 Comparison type mismatch smallint = int 150
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 152
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 162
 QTYP 4 Comparison type mismatch smallint = int 162
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 164
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 166
 MGTP 3 Grant to public master..sysattributes  
 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_dropdevice  
 MLCH 3 Char type with length>30 char(40) 160
 MNER 3 No Error Check should check @@error after delete 187
 MNER 3 No Error Check should check return value of exec 195
 MUCO 3 Useless Code Useless Brackets 42
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 190
 MUCO 3 Useless Code Useless Brackets 198
 QCRS 3 Conditional Result Set 124
 QCRS 3 Conditional Result Set 135
 QCRS 3 Conditional Result Set 160
 QISO 3 Set isolation level 49
 QNAJ 3 Not using ANSI Inner Join 100
 QNAJ 3 Not using ANSI Inner Join 116
 QNAJ 3 Not using ANSI Inner Join 127
 QNAJ 3 Not using ANSI Inner Join 138
 QNAJ 3 Not using ANSI Inner Join 161
 QNAM 3 Select expression has no name convert(char(30), name) 160
 QNAM 3 Select expression has no name convert(char(40), char_value) 160
 QNUA 3 Should use Alias: Column size should use alias b 126
 QNUA 3 Should use Alias: Column size should use alias b 137
 QNUA 3 Should use Alias: Column char_value should use alias b 160
 QNUA 3 Should use Alias: Column name should use alias a 160
 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: {object_type, object_cinfo, attribute, class}
150
 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: {object_type, object_cinfo, attribute, class}
162
 VNRD 3 Variable is not read @dummy 72
 MRST 2 Result Set Marker 124
 MRST 2 Result Set Marker 135
 MRST 2 Result Set Marker 160
 MSUB 2 Subquery Marker 84
 MSUB 2 Subquery Marker 99
 MSUB 2 Subquery Marker 148
 MTR1 2 Metrics: Comments Ratio Comments: 30% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 19 = 24dec - 7exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 113 14
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, d2=master..sysdevices, u=master..sysusages} 0 99
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, d2=master..sysdevices, u=master..sysusages} 0 114
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, d2=master..sysdevices, u=master..sysusages} 0 124
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, d2=master..sysdevices, u=master..sysusages} 0 135
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, d=master..sysdatabases} 0 160

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_dropdevice_rset_002 
writes table sybsystemprocs..sp_dropdevice_rset_001 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
writes table sybsystemprocs..sp_dropdevice_rset_003 
read_writes table master..sysdevices (1)  
reads table master..sysattributes (1)  
reads table master..sysdatabases (1)  
reads table master..sysusages (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)