DatabaseProcApplicationCreatedLinks
sybsystemprocssp_primarykey  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/password */
4     
5     /*
6     ** Messages for "sp_primarykey"         17740
7     **
8     ** 17390, "Table or view name must be in 'current' database." 
9     ** 17492, "The table or view named doesn't exist in the current database." 
10    ** 17740, "Only the owner of the table may define a primary key."
11    ** 17741, "Primary key already exists on table -- drop key first."
12    ** 17742, "The table has no such first column."
13    ** 17743, "The table has no such second column."
14    ** 17744, "The table has no such third column."
15    ** 17745, "The table has no such fourth column."
16    ** 17746, "The table has no such fifth column."
17    ** 17747, "The table has no such sixth column."
18    ** 17748, "The table has no such seventh column."
19    ** 17749, "The table has no such eighth column."
20    ** 17750, "New primary key added."
21    ** 17756, "The execution of the stored procedure '%1!' in database
22    **         '%2!' was aborted because there was an error in writing the
23    **         replication log record."
24    */
25    
26    /*
27    ** IMPORTANT NOTE:
28    ** This stored procedure uses the built-in function object_id() in the
29    ** where clause of a select query. If you intend to change this query
30    ** or use the object_id() or db_id() builtin in this procedure, please read the
31    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
32    ** pertaining to object-id's and db-id's outlined there, are followed.
33    */
34    
35    create procedure sp_primarykey
36        @tabname varchar(767), /* table name that has the key */
37        @col1 varchar(255), /* columns that make up the key */
38        @col2 varchar(255) = NULL,
39        @col3 varchar(255) = NULL,
40        @col4 varchar(255) = NULL,
41        @col5 varchar(255) = NULL,
42        @col6 varchar(255) = NULL,
43        @col7 varchar(255) = NULL,
44        @col8 varchar(255) = NULL
45    as
46    
47        declare @uid int /* owner id of the object */
48        declare @cnt smallint /* howmany columns in key */
49        declare @key1 smallint /* colids of the columns in the key */
50        declare @key2 smallint
51        declare @key3 smallint
52        declare @key4 smallint
53        declare @key5 smallint
54        declare @key6 smallint
55        declare @key7 smallint
56        declare @key8 smallint
57        declare @msg varchar(1024)
58        declare @dbname varchar(255)
59    
60    
61        if @@trancount = 0
62        begin
63            set chained off
64        end
65    
66        set transaction isolation level 1
67    
68        /*
69        **  Check to see that the tabname is local.
70        */
71        if @tabname like "%.%.%" and
72            substring(@tabname, 1, charindex(".", @tabname) - 1) != db_name()
73        begin
74            /*
75            ** 17390, "Table or view name must be in 'current' database." 
76            */
77            raiserror 17390
78            return (1)
79        end
80    
81        /*
82        **  See if we can find the object.  It must be a system table, user table,
83        **  or view.  The low 3 bits of sysobjects.sysstat indicate what the 
84        **  object type is -- it's more reliable than using sysobjects.type which
85        **  could change.
86        */
87        if not exists (select *
88                from sysobjects
89                where id = object_id(@tabname)
90                    and (sysstat & 7 = 1 /* system table */
91                        or sysstat & 7 = 2 /* view */
92                        or sysstat & 7 = 3)) /* user table */
93        begin
94            /*
95            ** 17492, "The table or view named doesn't exist in the current database." 
96            */
97            raiserror 17492
98            return (1)
99        end
100   
101       /*
102       **  Check to see that object owner is adding the key.
103       */
104       select @uid = uid
105       from sysobjects
106       where id = object_id(@tabname)
107           and (sysstat & 7 = 1 /* system table */
108               or sysstat & 7 = 2 /* view */
109               or sysstat & 7 = 3) /* user table */
110       if @uid != user_id()
111       begin
112           /*
113           ** 17740, "Only the owner of the table may define a primary key."
114           */
115           raiserror 17740
116           return (1)
117       end
118   
119       /*
120       **  See if the primary key exists already.  Syskeys.type = 1 indicates
121       **  a primary key.
122       */
123       if exists (select *
124               from syskeys
125               where id = object_id(@tabname)
126                   and type = 1)
127       begin
128           /*
129           ** 17741, "Primary key already exists on table -- drop key first."
130           */
131           raiserror 17741
132           return (1)
133       end
134   
135       /*
136       **  Now check out each column argument to verify it's existence.
137       */
138       select @cnt = 1, @key1 = colid
139       from syscolumns
140       where name = @col1
141           and id = object_id(@tabname)
142       if @key1 is NULL
143       begin
144           /*
145           ** 17742, "The table has no such first column."
146           */
147           raiserror 17742
148           return (1)
149       end
150   
151       if @col2 is not NULL
152       begin
153           select @cnt = @cnt + 1, @key2 = colid
154           from syscolumns
155           where name = @col2
156               and id = object_id(@tabname)
157           if @key2 is NULL
158           begin
159               /*
160               ** 17743, "The table has no such second column."
161               */
162               raiserror 17743
163               return (1)
164           end
165       end
166       else goto doinsert
167   
168       if @col3 is not NULL
169       begin
170           select @cnt = @cnt + 1, @key3 = colid
171           from syscolumns
172           where name = @col3
173               and id = object_id(@tabname)
174           if @key3 is NULL
175           begin
176               /*
177               ** 17744, "The table has no such third column."
178               */
179               raiserror 17744
180               return (1)
181           end
182       end
183       else goto doinsert
184   
185       if @col4 is not NULL
186       begin
187           select @cnt = @cnt + 1, @key4 = colid
188           from syscolumns
189           where name = @col4
190               and id = object_id(@tabname)
191           if @key4 is NULL
192           begin
193               /*
194               ** 17745, "The table has no such fourth column."
195               */
196               raiserror 17745
197               return (1)
198           end
199       end
200       else goto doinsert
201   
202       if @col5 is not NULL
203       begin
204           select @cnt = @cnt + 1, @key5 = colid
205           from syscolumns
206           where name = @col5
207               and id = object_id(@tabname)
208           if @key5 is NULL
209           begin
210               /*
211               ** 17746, "The table has no such fifth column."
212               */
213               raiserror 17746
214               return (1)
215           end
216       end
217       else goto doinsert
218   
219       if @col6 is not NULL
220       begin
221           select @cnt = @cnt + 1, @key6 = colid
222           from syscolumns
223           where name = @col6
224               and id = object_id(@tabname)
225           if @key6 is NULL
226           begin
227               /*
228               ** 17747, "The table has no such sixth column."
229               */
230               raiserror 17747
231               return (1)
232           end
233       end
234       else goto doinsert
235   
236       if @col7 is not NULL
237       begin
238           select @cnt = @cnt + 1, @key7 = colid
239           from syscolumns
240           where name = @col7
241               and id = object_id(@tabname)
242           if @key7 is NULL
243           begin
244               /*
245               ** 17748, "The table has no such seventh column."
246               */
247               raiserror 17748
248               return (1)
249           end
250       end
251       else goto doinsert
252   
253       if @col8 is not NULL
254       begin
255           select @cnt = @cnt + 1, @key8 = colid
256           from syscolumns
257           where name = @col8
258               and id = object_id(@tabname)
259           if @key8 is NULL
260           begin
261               /*
262               ** 17749, "The table has no such eighth column."
263               */
264               raiserror 17749
265               return (1)
266           end
267       end
268   
269       /*
270       **  Type 1 is a primary key, 2 is a foreignkey, and 3 is a commonjoin.
271       */
272   doinsert:
273   
274       /* 
275       ** This transaction also writes a log record for replicating the
276       ** invocation of this procedure. If logexec() fails, the transaction	
277       ** is aborted.
278       **
279       ** IMPORTANT: The name rs_logexec is significant and is used by
280       ** Replication Server.
281       */
282       begin transaction rs_logexec
283   
284       insert into syskeys
285       (id, type, depid, keycnt, size, key1, key2, key3, key4, key5,
286           key6, key7, key8, spare1)
287       values (object_id(@tabname), 1, NULL, @cnt, 0, @key1, @key2, @key3, @key4,
288           @key5, @key6, @key7, @key8, 0)
289   
290       if (@@error != 0)
291       begin
292           rollback transaction rs_logexec
293           return (1)
294       end
295   
296       /*
297       ** Write the log record to replicate this invocation 
298       ** of the stored procedure.
299       */
300       if (logexec() != 1)
301       begin
302           /*
303           ** 17756, "The execution of the stored procedure '%1!' in
304           **         database '%2!' was aborted because there was an
305           **         error in writing the replication log record."
306           */
307           select @dbname = db_name()
308           raiserror 17756, "sp_primarykey", @dbname
309   
310           rollback transaction rs_logexec
311           return (1)
312       end
313   
314       commit transaction
315   
316       /*
317       ** 17750, "New primary key added."
318       */
319       exec sp_getmessage 17750, @msg output
320       print @msg
321   
322       return (0)
323   


exec sp_procxmode 'sp_primarykey', 'AnyMode'
go

Grant Execute on sp_primarykey to public
go
DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 126
 TNOU 4 Table with no unique index sybsystemprocs..syskeys sybsystemprocs..syskeys
 MGTP 3 Grant to public sybsystemprocs..sp_primarykey  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..syskeys  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 319
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 248
 MUCO 3 Useless Code Useless Brackets 265
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 293
 MUCO 3 Useless Code Useless Brackets 300
 MUCO 3 Useless Code Useless Brackets 311
 MUCO 3 Useless Code Useless Brackets 322
 QAFM 3 Var Assignment from potentially many rows 138
 QAFM 3 Var Assignment from potentially many rows 153
 QAFM 3 Var Assignment from potentially many rows 170
 QAFM 3 Var Assignment from potentially many rows 187
 QAFM 3 Var Assignment from potentially many rows 204
 QAFM 3 Var Assignment from potentially many rows 221
 QAFM 3 Var Assignment from potentially many rows 238
 QAFM 3 Var Assignment from potentially many rows 255
 QISO 3 Set isolation level 66
 QIWC 3 Insert with not all columns specified missing 8 columns out of 22 285
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
140
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
155
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
172
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
189
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
206
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
223
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
240
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
257
 MSUB 2 Subquery Marker 87
 MSUB 2 Subquery Marker 123
 MTR1 2 Metrics: Comments Ratio Comments: 47% 35
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 38dec - 15exi + 2 35
 MTR3 2 Metrics: Query Complexity Complexity: 150 35

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..syskeys  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..sysobjects