DatabaseProcApplicationCreatedLinks
sybsystemprocssp_aux_parse_entity  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Auxilliary stored procedure for parsing entities.  Entities
4     ** may either be databases, tables, or indices.
5     **
6     ** The following assumptions will be made:
7     **	1) If the database parameter is NULL, it is assumed that the entity
8     **	   being addressed belongs in the current database.
9     **
10    ** Input Parameters:
11    **	dbname		database name (NULL if none specified)
12    **	tablename	NULL if no table is being specified
13    **				(Note that this indicates that the entity
14    **				 is a database.  This also requires that
15    **				 the indexname parameter be NULL.)
16    **			table name otherwise
17    **				(specified in the following format:
18    **					`[owner_name.]table_name'
19    **				 Note that database name is not accepted.)
20    **	indexname	NULL or "table only" if index not being specified
21    **			"text only" if specifying text/image pages only
22    **			name of index otherwise
23    ** 
24    ** Output parameters:
25    **	dbid		database id
26    **	ownerid		user id of owner of entity
27    **	objid		NULL if no table specified
28    **			object id otherwise
29    **	indid		NULL if no index specified
30    **			index id otherwise (index id 255 used to specify
31    **			text/image pages)
32    **	entitytype	`D' for databases
33    **			`T' for tables
34    **			`I' for indices
35    ** Returns:
36    **	0		everything went ok
37    **	1		general error
38    **	2		syntax error - raising a usage message is
39    **			*REQUIRED* after one of these.
40    */
41    
42    /*
43    ** Messages for "sp_aux_parse_entity"
44    **
45    ** 17460, "Object must be in the current database."
46    ** 18088, "The target database does not exist."
47    ** 18090, "The target object does not exist."
48    ** 18091, "The target index does not exist."
49    ** 18154, "`%1!' is not a table."
50    ** 18171, "The table must have a text column if `text only' is specified."
51    */
52    
53    create or replace procedure sp_aux_parse_entity
54        @dbname varchar(255) = NULL,
55        @tablename varchar(512) = NULL,
56        @indexname varchar(255) = NULL,
57        @dbid int output,
58        @ownerid int output,
59        @objid int output,
60        @indid int output,
61        @entitytype char(1) output
62    as
63    
64        declare @tablestring varchar(767)
65        declare @objecttype char(2)
66    
67        select @dbid = NULL
68        select @ownerid = NULL
69        select @objid = NULL
70        select @indid = NULL
71        select @entitytype = NULL
72    
73        /*
74        ** Perform generic syntax checks on the arguments.
75        */
76        if (@dbname like "%.%")
77            or (@indexname like "%.%")
78            or (@tablename like ".%")
79            or (@tablename like "%.")
80            or (@tablename like "%.%.%")
81        begin
82            /*
83            ** No need for us to raise a syntax error message here.
84            ** Let the caller raise an error message for us.
85            */
86            return 2
87        end
88    
89        /*
90        ** Check for a database entity
91        */
92        if @tablename is NULL
93        begin
94            /*
95            ** Syntax error checking.
96            */
97            if @dbname is NULL
98            begin
99                /*
100               ** No need for us to raise a syntax error message here.
101               ** Let the caller raise an error message for us.
102               */
103               return 2
104           end
105   
106           if @indexname is not NULL
107           begin
108               /*
109               ** No need for us to raise a syntax error message here.
110               ** Let the caller raise an error message for us.
111               */
112               return 2
113           end
114   
115           /*
116           ** This is a database entity.  See if the database exists.
117           */
118           select @dbid = db_id(@dbname)
119           if @dbid is NULL
120           begin
121               /* 18088, "The target database does not exist." */
122               raiserror 18088
123               return 1
124           end
125   
126           /*
127           ** Set the output parameters and return
128           */
129           select @ownerid = user_id("dbo")
130           select @entitytype = 'D'
131       end
132       else
133       begin
134           /*
135           ** The entity is either a table or an index.
136           */
137   
138           /*
139           ** In both instances we need to figure out the table id.
140           ** In order to do this, first, make sure we are only affecting
141           ** objects in the current database!
142           */
143           if @dbname is not NULL
144           begin
145               if @dbname != db_name()
146               begin
147                   /* 17460, "Object must be in the current database." */
148                   raiserror 17460
149                   return 1
150               end
151           end
152           else
153           begin
154               select @dbname = db_name()
155           end
156   
157           select @dbid = db_id()
158   
159           /*
160           ** Next, formulate the fully-qualified table name.
161           */
162           if @tablename like "%.%"
163           begin
164               select @tablestring = @dbname + "." + @tablename
165           end
166           else
167           begin
168               select @tablestring = @dbname + "." + user_name() + "." +
169                   @tablename
170           end
171   
172           /*
173           ** Retrieve the id from the fully-qualified table name.
174           */
175           select @objid = object_id(@tablestring)
176   
177           /*
178           ** Check for existence.
179           */
180           if @objid is NULL
181           begin
182               /* 18090, "The target object does not exist."*/
183               raiserror 18090
184               return 1
185           end
186   
187           /*
188           ** Retrieve the owner and type of this object.  Make sure that 
189           ** the object is indeed a table.
190           */
191           select @ownerid = uid, @objecttype = type
192           from sysobjects
193           where (id = @objid)
194   
195           if (@objecttype != 'S') and (@objecttype != 'U')
196           begin
197               /* 18154, "`%1!' is not a table." */
198               raiserror 18154, @tablestring
199               return 1
200           end
201   
202           /*
203           ** Now handle the index special case, if any.
204           */
205           if (@indexname is not NULL) and (lower(@indexname) != "table only")
206           begin
207               /*
208               ** An index has been specified.  Check for the special
209               ** "text only" case.
210               */
211               if lower(@indexname) = "text only"
212               begin
213                   /*
214                   ** Check if the table has a text/image column.
215                   */
216                   if not exists (select *
217                           from sysindexes
218                           where (indid = 255) and
219                               (id = @objid))
220                   begin
221                       /* 18171, "The table must have a text column if `text only' is specified." */
222                       raiserror 18171
223                       return 1
224                   end
225                   else
226                   begin
227                       select @indid = 255
228                   end
229               end
230               else
231               begin
232                   /*
233                   ** Grab the index id.
234                   */
235                   select @indid = indid from sysindexes
236                   where (id = @objid) and (name = @indexname) and (indid > 0)
237   
238                   /*
239                   ** Check for existence.
240                   */
241                   if @indid is NULL
242                   begin
243   
244                       /* 18091, "The target index does not exist." */
245                       raiserror 18091
246                       return 1
247                   end
248               end
249               select @entitytype = 'I'
250           end
251           else
252           begin
253               select @entitytype = 'T'
254           end
255       end
256   
257       return 0
258   

DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 218
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 236
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 193
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 219
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 236
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MUCO 3 Useless Code Useless Brackets 193
 MSUB 2 Subquery Marker 216
 MTR1 2 Metrics: Comments Ratio Comments: 57% 53
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 16 = 24dec - 10exi + 2 53
 MTR3 2 Metrics: Query Complexity Complexity: 80 53

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysindexes  
reads table sybsystemprocs..sysobjects  

CALLERS
called by proc sybsystemprocs..sp_bindcache  
   called by proc sybsystemprocs..sp_unbindcache  
called by proc sybsystemprocs..sp_cachestrategy