DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropdevice  31 Aug 14Defects 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 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    
25        select @adbclass = 28
26    
27        /*
28        **  If we're in a transaction, disallow this since it might make recovery
29        **  impossible.
30        */
31        if @@trancount > 0
32        begin
33            /*
34            ** 17260, "Can't run %1! from within a transaction." 
35            */
36            raiserror 17260, "sp_dropdevice"
37            return (1)
38        end
39        else
40        begin
41            set chained off
42        end
43    
44        set transaction isolation level 1
45    
46        /* check if user has sa role, proc_role will also do auditing
47        ** if required. proc_role will also print error message if required.
48        */
49    
50        if (proc_role("sa_role") = 0)
51            return (1)
52    
53        /*
54        **  Check and make sure that the device actually exists.
55        */
56        if not exists (select *
57                from master.dbo.sysdevices
58                where name = @logicalname)
59        begin
60            /*
61            ** 17471, "No such device exists -- run sp_helpdevice to list the DataServer devices." 
62            */
63            raiserror 17471
64            return (1)
65        end
66    
67        /*
68        **  Check and make sure that no database is using the device.
69        **  If so, print out who is using it and exit.
70        */
71        if exists (select *
72                from master.dbo.sysdatabases a, master.dbo.sysusages b,
73                    master.dbo.sysdevices c
74                where a.dbid = b.dbid
75                    and b.vdevno = c.vdevno
76                    and ((c.status & 6 != 0) or (c.status2 & 8 != 0))
77                    and c.name = @logicalname)
78        begin
79            /*
80            ** 17482, "Device is being used by a database. You can't drop it."
81            */
82            raiserror 17482
83    
84            select @factor = @@maxpagesize / @@pagesize
85    
86            select @len1 = max(datalength(a.name)),
87                @len2 = max(datalength(c.name))
88            from master.dbo.sysdatabases a, master.dbo.sysusages b,
89                master.dbo.sysdevices c
90            where a.dbid = b.dbid
91                and b.vdevno = c.vdevno
92                and ((c.status & 6 != 0) or (c.status2 & 8 != 0))
93                and c.name = @logicalname
94    
95            if (@len1 > 23 or @len2 > 21)
96                select database_name = a.name,
97                    device = c.name,
98                    size = convert(varchar(25), (size / 512) * @factor) + " MB"
99                from master.dbo.sysdatabases a, master.dbo.sysusages b,
100                   master.dbo.sysdevices c
101               where a.dbid = b.dbid
102                   and b.vdevno = c.vdevno
103                   and ((c.status & 6 != 0) or (c.status2 & 8 != 0))
104                   and c.name = @logicalname
105               order by a.name, c.name, size
106           else
107               select database_name = convert(char(23), a.name),
108                   device = convert(char(21), c.name),
109                   size = convert(varchar(25), (size / 512) * @factor) + " MB"
110               from master.dbo.sysdatabases a, master.dbo.sysusages b,
111                   master.dbo.sysdevices c
112               where a.dbid = b.dbid
113                   and b.vdevno = c.vdevno
114                   and ((c.status & 6 != 0) or (c.status2 & 8 != 0))
115                   and c.name = @logicalname
116               order by a.name, c.name, size
117   
118           return (1)
119       end
120       else if exists (select 1
121               from master..sysattributes a
122               where a.class = @adbclass
123                   and a.object_type = 'D'
124                   and a.attribute = 1
125                   and a.object_cinfo = @logicalname)
126       begin
127           /*
128           ** 17482, "Device is being used by a database. You can't drop it."
129           */
130           raiserror 17482
131   
132           select convert(char(30), name), convert(char(40), char_value)
133           from master.dbo.sysdatabases a, master.dbo.sysattributes b
134           where b.class = @adbclass
135               and b.object_type = 'D'
136               and b.attribute = 1
137               and b.object_cinfo = @logicalname
138               and b.object = a.dbid
139   
140           return (1)
141       end
142   
143       /*
144       **  Everything's ok so drop the device.
145       */
146       select @status = status from master.dbo.sysdevices
147       where name = @logicalname
148   
149       select @status2 = status2 from master.dbo.sysdevices
150       where name = @logicalname
151       /*
152       ** If database device or cache device (status2 & 8), close it and remove it 
153       ** from the sysdevices table using "disk release" command.  If dump device,
154       ** remove it from sysdevices table.
155       **/
156       if (((@status & 6) != 0) or ((@status2 & 8) != 0))
157           disk release name = @logicalname
158       else
159           delete master.dbo.sysdevices
160           where name = @logicalname
161       if (@@error != 0)
162           return (1)
163   
164       /*
165       ** 17483, "Device dropped."
166       */
167       exec sp_getmessage 17483, @msg output
168       print @msg
169   
170       return (0)
171   


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 122
 QTYP 4 Comparison type mismatch smallint = int 122
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 124
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 134
 QTYP 4 Comparison type mismatch smallint = int 134
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 136
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 138
 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) 132
 MNER 3 No Error Check should check @@error after delete 159
 MNER 3 No Error Check should check return value of exec 167
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 50
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 162
 MUCO 3 Useless Code Useless Brackets 170
 QCRS 3 Conditional Result Set 96
 QCRS 3 Conditional Result Set 107
 QCRS 3 Conditional Result Set 132
 QISO 3 Set isolation level 44
 QNAJ 3 Not using ANSI Inner Join 72
 QNAJ 3 Not using ANSI Inner Join 88
 QNAJ 3 Not using ANSI Inner Join 99
 QNAJ 3 Not using ANSI Inner Join 110
 QNAJ 3 Not using ANSI Inner Join 133
 QNAM 3 Select expression has no name convert(char(30), name) 132
 QNAM 3 Select expression has no name convert(char(40), char_value) 132
 QNUA 3 Should use Alias: Column size should use alias b 98
 QNUA 3 Should use Alias: Column size should use alias b 109
 QNUA 3 Should use Alias: Column char_value should use alias b 132
 QNUA 3 Should use Alias: Column name should use alias a 132
 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}		 122
 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}		 134
 MRST 2 Result Set Marker 96
 MRST 2 Result Set Marker 107
 MRST 2 Result Set Marker 132
 MSUB 2 Subquery Marker 56
 MSUB 2 Subquery Marker 71
 MSUB 2 Subquery Marker 120
 MTR1 2 Metrics: Comments Ratio Comments: 30% 14
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 18 = 21dec - 5exi + 2 14
 MTR3 2 Metrics: Query Complexity Complexity: 102 14
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, d2=master..sysdevices, u=master..sysusages} 0 71
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, d2=master..sysdevices, u=master..sysusages} 0 86
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, d2=master..sysdevices, u=master..sysusages} 0 96
 PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, d2=master..sysdevices, u=master..sysusages} 0 107
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, d=master..sysdatabases} 0 132

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
reads table master..sysusages (1)  
read_writes table master..sysdevices (1)  
reads table master..sysdatabases (1)  
reads table master..sysattributes (1)