DatabaseProcApplicationCreatedLinks
sybsystemprocssp_defaultloc  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     /*
5     ** Omni only
6     **
7     ** Messages for "sp_defaultloc"	 18322
8     **
9     ** 17240, "'%1!' is not a valid name."
10    ** 17800, "No such server -- run sp_helpserver to list servers."
11    ** 18322, "There is not a database named '%1!'."
12    ** 18323, "There is not an object type named '%1!'."
13    ** 18324, "An RMS filespec for a directory must be specified."
14    ** 18325, "Storage location syntax must end in '.'."
15    ** 18326, "Created default location for database '%1!' at '%2!'."
16    ** 18327, "You cannot remap objects in the master, model, or tempdb databases."
17    ** 18328, "There is no default storage location in effect for database '%1!'."
18    ** 18329, "Default storage location deleted for database '%1!'."
19    ** 18330, "Database '%1!' already has a default location '%2!'."
20    */
21    
22    create or replace procedure sp_defaultloc
23        @dbname varchar(30), /* server name */
24        @defaultloc varchar(255), /* default storage location */
25        @defaulttype varchar(30) = "table" /* default object type */
26    as
27        begin
28            declare @dbid smallint,
29                @type int,
30                @last_char char(1),
31                @msg varchar(1024),
32                @oldloc varchar(255)
33            declare @site varchar(30),
34                @db varchar(30),
35                @own varchar(30),
36                @obj varchar(30),
37                @status int,
38                @isatempdb int
39            declare @tempdb_mask int /* All database status bit for a tempdb */
40    
41            /*
42            ** Get the database id.
43            */
44            select @dbid = 0
45            select @dbid = db_id(@dbname)
46            if @dbid is null
47            begin
48                /* 
49                ** 18322, "There is not a database named '%1!'."
50                */
51                raiserror 18322, @dbname
52                return (1)
53            end
54    
55            /*
56            ** Determine if we are mapping a temporary database.
57            */
58            select @tempdb_mask = number
59            from master.dbo.spt_values
60            where type = "D3" and name = "TEMPDB STATUS MASK"
61    
62            if (@dbid = 2) or exists (select * from master.dbo.sysdatabases
63                    where dbid = @dbid
64                        and (status3 & @tempdb_mask) != 0)
65            begin
66                select @isatempdb = 1
67            end
68    
69            /*
70            ** If the database is master, model or tempdb, disallow this.
71            */
72            if @dbname = "master" OR @dbname = "model" OR @isatempdb = 1
73            begin
74                /* 
75                ** 18327, "You cannot remap objects in the master, model, or tempdb databases."
76                */
77                raiserror 18327
78                return (1)
79            end
80    
81            /*
82            ** If the database is an IQ catalog database, disallow this.
83            */
84            exec @status = sp_iqdbcheck @dbname
85            if @status = 1
86            begin
87                return 1
88            end
89    
90            /*
91            ** Get a value @type from @objecttype
92            */
93            select @type = number from master.dbo.spt_values
94            where type = 'Y' and name = lower(@defaulttype)
95    
96            if @@rowcount = 0
97            begin
98                /* 
99                ** 18323, "There is not an object type named '%1!'."
100               */
101               raiserror 18323, @defaulttype
102               return (1)
103           end
104   
105           /*
106           ** If the defaultloc is NULL, then delete any default location that
107           ** may exist for this database.
108           */
109           if @defaultloc is NULL
110           begin
111               if exists (select * from master.dbo.sysdatabases
112                       where dbid = @dbid
113                           and def_remote_loc is null)
114               begin
115                   /* 
116                   ** 18328, "There is no default storage location in effect for database '%1!'."
117                   */
118                   raiserror 18328, @dbname
119                   return (1)
120               end
121               else
122               begin
123                   update master.dbo.sysdatabases
124                   set def_remote_loc = NULL,
125                       def_remote_type = NULL
126                   where dbid = @dbid
127   
128                   /* 
129                   ** 18329, "Default storage location deleted for database '%1!'."
130                   */
131                   exec sp_getmessage 18329, @msg output
132                   print @msg, @dbname
133                   checkpoint @dbname
134                   return (0)
135               end
136           end
137   
138           /*
139           ** If the object type is not a file, the syntax of the location
140           ** field must be 'server.dbname.owner.', where server must be
141           ** provided, and dbname and/or owner may be omitted.
142           */
143           select @last_char = SUBSTRING(@defaultloc, CHAR_LENGTH(@defaultloc), 1)
144   
145           /*
146           ** If the type is FILE (2), then the last character must be a
147           ** ']', since the default loc must be an RMS filespec for a 
148           ** directory.
149           */
150   
151           if exists (select * from master.dbo.spt_values where type = 'E' and name = 'vms')
152           begin
153               if @type = 2
154               begin
155                   if @last_char != ']' AND @last_char != ':'
156                   begin
157                       /* 
158                       ** 18324, "An RMS filespec for a directory must be specified."
159                       */
160                       raiserror 18324
161                       return (1)
162                   end
163               end
164           end
165           /*
166           ** If the type is FILE (2), and we are on unix then we allow
167           ** anything and validate inside the omni code
168           */
169   
170           if @type != 2
171           begin
172               if @last_char != '.'
173               begin
174                   /* 
175                   ** 18325, "Storage location syntax must end in '.'."
176                   */
177                   raiserror 18325
178                   return (1)
179               end
180   
181               /*
182               ** Make sure the format is correct: "SERVER.db.owner.",
183               */
184               exec @status = sp_namecrack @defaultloc,
185                   @site output, @db output, @own output, @obj output
186   
187               if @status != 0 OR @obj IS NOT NULL OR @site IS NULL
188               begin
189                   /*
190                   ** 17240, "'%1!' is not a valid name."
191                   */
192                   raiserror 17240, @defaultloc
193                   return (1)
194               end
195               if not exists (select * from master.dbo.sysservers
196                       where srvname = @site)
197               begin
198                   /*
199                   ** 17800, "No such server -- run sp_helpserver to
200                   ** list servers."
201                   */
202                   raiserror 17800
203                   return (1)
204   
205               end
206           end
207   
208           if exists (select * from master.dbo.sysdatabases
209                   where dbid = @dbid and def_remote_loc is not null)
210           begin
211               select @oldloc = def_remote_loc from master.dbo.sysdatabases
212               where dbid = @dbid
213               /* 
214               ** 18330, "Database '%1!' already has a default location '%2!'."
215               */
216               raiserror 18330, @dbname, @oldloc
217               return (1)
218           end
219           else
220           begin
221               update master.dbo.sysdatabases
222               set def_remote_loc = @defaultloc,
223                   def_remote_type = @type
224               where dbid = @dbid
225               /* 
226               ** 18326, "Created default location for database '%1!' at '%2!'." 
227               */
228               exec sp_getmessage 18326, @msg output
229               print @msg, @dbname, @defaultloc
230               checkpoint @dbname
231           end
232   
233           return (0)
234       end
235   
236   
237   
238   


exec sp_procxmode 'sp_defaultloc', 'AnyMode'
go

Grant Execute on sp_defaultloc to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @oldloc: varchar(255) = varchar(349) 211
 MTYP 4 Assignment type mismatch def_remote_type: smallint = int 223
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
60
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
94
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
151
 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..sysdatabases  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_defaultloc  
 MNER 3 No Error Check should check @@error after update 123
 MNER 3 No Error Check should check return value of exec 131
 MNER 3 No Error Check should check @@error after update 221
 MNER 3 No Error Check should check return value of exec 228
 MUCO 3 Useless Code Useless Begin-End Pair 27
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 217
 MUCO 3 Useless Code Useless Brackets 233
 MUOT 3 Updates outside transaction 221
 QAFM 3 Var Assignment from potentially many rows 58
 QAFM 3 Var Assignment from potentially many rows 93
 VNRD 3 Variable is not read @db 185
 VNRD 3 Variable is not read @own 185
 MSUB 2 Subquery Marker 62
 MSUB 2 Subquery Marker 111
 MSUB 2 Subquery Marker 151
 MSUB 2 Subquery Marker 195
 MSUB 2 Subquery Marker 208
 MTR1 2 Metrics: Comments Ratio Comments: 45% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 18 = 28dec - 12exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 104 22

DATA PROPAGATION detailed
ColumnWritten To
@defaultlocsysdatabases.def_remote_loc   sp_helpdb_rset_001.sqlbNoName73 sp_helpdb_rset_002.template_database

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
read_writes table master..sysdatabases (1)  
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_namecrack  
reads table master..sysservers (1)  
calls proc sybsystemprocs..sp_iqdbcheck