DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_fkeys  14 déc. 14Defects Propagation Dependencies

1     
2     /*-----------------------Foreign Key----------------------------*/
3     /* sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
4     /*
5     ** note: there is one raiserror message: 18040
6     **
7     ** messages for "sp_oledb_fkeys"               18039, 18040
8     **
9     ** 17461, "Object does not exist in this database." 
10    ** 18040, "Catalog procedure %1! can not be run in a transaction.", sp_oledb_fkeys
11    ** 18043 " Primary key table name or foreign key table name or both must be 
12    ** given"
13    ** 18044, "%1! table qualifier must be name of current database." [Primary
14    ** key | Foreign key]
15    **
16    */
17    CREATE OR REPLACE PROCEDURE sp_oledb_fkeys
18        @pktable_name varchar(255) = null,
19        @pktable_owner varchar(32) = null,
20        @pktable_qualifier varchar(32) = null,
21        @fktable_name varchar(255) = null,
22        @fktable_owner varchar(32) = null,
23        @fktable_qualifier varchar(32) = null
24    as
25        declare @ftabid int, @ptabid int, @constrid int, @keycnt int, @i int
26        declare @fokey1 int, @fokey2 int, @fokey3 int, @fokey4 int, @fokey5 int
27        declare @fokey6 int, @fokey7 int, @fokey8 int, @fokey9 int, @fokey10 int
28        declare @fokey11 int, @fokey12 int, @fokey13 int, @fokey14 int, @fokey15 int
29        declare @refkey1 int, @refkey2 int, @refkey3 int, @refkey4 int, @refkey5 int
30        declare @refkey6 int, @refkey7 int, @refkey8 int, @refkey9 int, @refkey10 int
31        declare @refkey11 int, @refkey12 int, @refkey13 int, @refkey14 int
32        declare @refkey15 int, @refkey16 int, @fokey16 int, @status int
33        declare @msg varchar(1024)
34        declare @msg2 varchar(1024)
35        declare @ordpkey int
36        declare @notDeferrable smallint
37        declare @startedInTransaction bit
38    
39        if (@@trancount > 0)
40            select @startedInTransaction = 1
41        else
42            select @startedInTransaction = 0
43    
44    
45        set nocount on
46    
47        if (@@trancount = 0)
48        begin
49            set chained off
50        end
51    
52    
53        set transaction isolation level 1
54        if (@startedInTransaction = 1)
55            save transaction oledb_keep_temptable_tx
56    
57        select @notDeferrable = 3
58    
59        create table #ofkey_res(PK_TABLE_CATALOG varchar(32),
60            PK_TABLE_SCHEMA varchar(32),
61            PK_TABLE_NAME varchar(255),
62            PK_COLUMN_NAME varchar(255),
63            PK_COLUMN_PROP_ID int null,
64            FK_TABLE_CATALOG varchar(32),
65            FK_TABLE_SCHEMA varchar(32),
66            FK_TABLE_NAME varchar(255),
67            FK_COLUMN_NAME varchar(255),
68            FK_COLUMN_PROP_ID int null,
69            ORDINAL int,
70            UPDATE_RULE varchar(32),
71            DELETE_RULE varchar(32),
72            FK_NAME varchar(255),
73            PK_NAME varchar(255),
74            DEFERRABILITY smallint)
75    
76        if (@pktable_name is null) and (@fktable_name is null)
77        begin
78            /* If neither primary key nor foreign key table names given */
79            /* goto select directly and return no rows
80            */
81            goto SelectClause
82        end
83    
84        if @fktable_qualifier is not null
85        begin
86            if db_name() != @fktable_qualifier
87            begin
88                goto SelectClause
89            end
90        end
91        else
92        begin
93            /*
94            ** Now make sure that foreign table qualifier is pointing to the
95            ** current database in case it is not specified.
96            */
97            select @fktable_qualifier = db_name()
98        end
99    
100       if @pktable_qualifier is not null
101       begin
102           if db_name() != @pktable_qualifier
103           begin
104               goto SelectClause
105           end
106       end
107       else
108       begin
109           /*
110           ** Now make sure that primary table qualifier is pointing to the
111           ** current database in case it is not specified.
112           */
113           select @pktable_qualifier = db_name()
114       end
115   
116   
117       create table #opid(pid int, uid int, name varchar(255))
118       create table #ofid(fid int, uid int, name varchar(255))
119   
120       /* we will sort by fkey		*/
121       /* unless pktable is null	*/
122   
123       select @ordpkey = 0
124   
125       if @pktable_name is not null
126       begin
127   
128           if (@pktable_owner is null)
129           begin
130               /* 
131               ** owner is NULL, so default to the current user
132               ** who owns this table, otherwise default to dbo
133               ** who owns this table.
134               */
135               insert into #opid
136               select id, uid, name
137               from sysobjects
138               where name = @pktable_name and uid = user_id()
139                   and type in ("S", "U")
140   
141               /* 
142               ** If the current user does not own the table, see
143               ** if the DBO of the current database owns the table.
144               */
145   
146               if ((select count(*) from #opid) = 0)
147               begin
148                   insert into #opid
149                   select id, uid, name
150                   from sysobjects
151                   where name = @pktable_name and uid = 1
152                       and type in ("S", "U")
153               end
154           end
155           else
156           begin
157               insert into #opid
158               select id, uid, name
159               from sysobjects
160               where name = @pktable_name
161                   and uid = user_id(@pktable_owner)
162                   and type in ("S", "U")
163           end
164       end
165       else
166       begin
167           if (@pktable_owner is null)
168           begin
169               /* 
170               ** If neither pktable_name nor pktable_owner is specified,
171               ** then we are interested in every user or system table.
172               */
173               insert into #opid
174               select id, uid, name
175               from sysobjects
176               where type in ("S", "U")
177           end
178           else
179           begin
180               insert into #opid
181               select id, uid, name
182               from sysobjects
183               where uid = user_id(@pktable_owner)
184                   and type in ("S", "U")
185           end
186       end
187   
188       if @fktable_name is not null
189       begin
190           /* sort by pkey	*/
191           select @ordpkey = 1
192   
193           if (@fktable_owner is null)
194           begin
195               /* 
196               ** owner is NULL, so default to the current user
197               ** who owns this table, otherwise default to dbo
198               ** who owns this table.
199               */
200               insert into #ofid
201               select id, uid, name
202               from sysobjects
203               where name = @fktable_name and uid = user_id()
204                   and type in ("S", "U")
205   
206               /* 
207               ** If the current user does not own the table, see
208               ** if the DBO of the current database owns the table.
209               */
210   
211               if ((select count(*) from #opid) = 0)
212               begin
213                   insert into #ofid
214                   select id, uid, name
215                   from sysobjects
216                   where name = @fktable_name and uid = 1
217                       and type in ("S", "U")
218               end
219           end
220           else
221           begin
222               insert into #ofid
223               select id, uid, name
224               from sysobjects
225               where name = @fktable_name
226                   and uid = user_id(@fktable_owner)
227                   and type in ("S", "U")
228           end
229       end
230       else
231       begin
232           if (@fktable_owner is null)
233           begin
234               /* 
235               ** If neither fktable_name nor fktable_owner is specified,
236               ** then we are interested in every user table or systme 
237               ** table.
238               */
239               insert into #ofid
240               select id, uid, name
241               from sysobjects
242               where type in ("S", "U")
243           end
244           else
245           begin
246               insert into #ofid
247               select id, uid, name
248               from sysobjects
249               where uid = user_id(@fktable_owner)
250                   and type in ("S", "U")
251           end
252       end
253   
254       if (((select count(*) from #ofid) = 0) or
255               ((select count(*) from #opid) = 0))
256       begin
257           goto SelectClause
258       end
259   
260   
261       create table #opkeys(seq int, keys varchar(255) null)
262       create table #ofkeys(seq int, keys varchar(255) null)
263   
264       /*
265       ** Since there are possibly multiple rows in sysreferences
266       ** that describe foreign and primary key relationships among
267       ** two tables, so we declare a cursor on the selection from
268       ** sysreferences and process the output at row by row basis.
269       */
270   
271       declare curs_sysreferences cursor
272       for
273       select tableid, reftabid, constrid, keycnt,
274           fokey1, fokey2, fokey3, fokey4, fokey5, fokey6, fokey7, fokey8,
275           fokey9, fokey10, fokey11, fokey12, fokey13, fokey14, fokey15,
276           fokey16, refkey1, refkey2, refkey3, refkey4, refkey5,
277           refkey6, refkey7, refkey8, refkey9, refkey10, refkey11,
278           refkey12, refkey13, refkey14, refkey15, refkey16
279       from sysreferences
280       where tableid in (
281               select fid from #ofid)
282           and reftabid in (
283               select pid from #opid)
284           and frgndbname is NULL and pmrydbname is NULL
285       for read only
286   
287       open curs_sysreferences
288   
289       fetch curs_sysreferences into @ftabid, @ptabid, @constrid, @keycnt, @fokey1,
290           @fokey2, @fokey3, @fokey4, @fokey5, @fokey6, @fokey7, @fokey8,
291           @fokey9, @fokey10, @fokey11, @fokey12, @fokey13, @fokey14, @fokey15,
292           @fokey16, @refkey1, @refkey2, @refkey3, @refkey4, @refkey5, @refkey6,
293           @refkey7, @refkey8, @refkey9, @refkey10, @refkey11, @refkey12,
294           @refkey13, @refkey14, @refkey15, @refkey16
295   
296       while (@@sqlstatus = 0)
297       begin
298           /*
299           ** For each row of sysreferences which describes a foreign-
300           ** primary key relationship, do the following.
301           */
302   
303           /*
304           ** First store the column names that belong to primary keys
305           ** in table #pkeys for later retrieval.
306           */
307   
308           delete #opkeys
309           insert #opkeys values (1, col_name(@ptabid, @refkey1))
310           insert #opkeys values (2, col_name(@ptabid, @refkey2))
311           insert #opkeys values (3, col_name(@ptabid, @refkey3))
312           insert #opkeys values (4, col_name(@ptabid, @refkey4))
313           insert #opkeys values (5, col_name(@ptabid, @refkey5))
314           insert #opkeys values (6, col_name(@ptabid, @refkey6))
315           insert #opkeys values (7, col_name(@ptabid, @refkey7))
316           insert #opkeys values (8, col_name(@ptabid, @refkey8))
317           insert #opkeys values (9, col_name(@ptabid, @refkey9))
318           insert #opkeys values (10, col_name(@ptabid, @refkey10))
319           insert #opkeys values (11, col_name(@ptabid, @refkey11))
320           insert #opkeys values (12, col_name(@ptabid, @refkey12))
321           insert #opkeys values (13, col_name(@ptabid, @refkey13))
322           insert #opkeys values (14, col_name(@ptabid, @refkey14))
323           insert #opkeys values (15, col_name(@ptabid, @refkey15))
324           insert #opkeys values (16, col_name(@ptabid, @refkey16))
325   
326           /*
327           ** Second store the column names that belong to foreign keys
328           ** in table #fkeys for later retrieval.
329           */
330   
331           delete #ofkeys
332           insert #ofkeys values (1, col_name(@ftabid, @fokey1))
333           insert #ofkeys values (2, col_name(@ftabid, @fokey2))
334           insert #ofkeys values (3, col_name(@ftabid, @fokey3))
335           insert #ofkeys values (4, col_name(@ftabid, @fokey4))
336           insert #ofkeys values (5, col_name(@ftabid, @fokey5))
337           insert #ofkeys values (6, col_name(@ftabid, @fokey6))
338           insert #ofkeys values (7, col_name(@ftabid, @fokey7))
339           insert #ofkeys values (8, col_name(@ftabid, @fokey8))
340           insert #ofkeys values (9, col_name(@ftabid, @fokey9))
341           insert #ofkeys values (10, col_name(@ftabid, @fokey10))
342           insert #ofkeys values (11, col_name(@ftabid, @fokey11))
343           insert #ofkeys values (12, col_name(@ftabid, @fokey12))
344           insert #ofkeys values (13, col_name(@ftabid, @fokey13))
345           insert #ofkeys values (14, col_name(@ftabid, @fokey14))
346           insert #ofkeys values (15, col_name(@ftabid, @fokey15))
347           insert #ofkeys values (16, col_name(@ftabid, @fokey16))
348   
349           /* 
350           ** For each column of the current foreign-primary key relation,
351           ** create a row into result table: #fkey_res.
352           */
353   
354           select @i = 1
355           while (@i <= @keycnt)
356           begin
357               insert into #ofkey_res
358               select @pktable_qualifier,
359                       (select user_name(uid) from #opid where pid = @ptabid),
360                   object_name(@ptabid),
361                       (select keys from #opkeys where seq = @i),
362                   null,
363                   @fktable_qualifier,
364                       (select user_name(uid) from #ofid where fid = @ftabid),
365                   object_name(@ftabid),
366                       (select keys from #ofkeys where seq = @i), null, @i,
367                   "NO_ACTION", "NO_ACTION",
368                   /* Foreign Key */
369                   object_name(@constrid),
370                       /* Primary key name */
371                       (select name from sysindexes where id = @ptabid
372                           and status > 2048 and status < 32768),
373                   @notDeferrable
374               select @i = @i + 1
375           end
376   
377           /* 
378           ** Go to the next foreign-primary key relationship if any.
379           */
380   
381           fetch curs_sysreferences into @ftabid, @ptabid, @constrid, @keycnt, @fokey1,
382               @fokey2, @fokey3, @fokey4, @fokey5, @fokey6, @fokey7, @fokey8,
383               @fokey9, @fokey10, @fokey11, @fokey12, @fokey13, @fokey14, @fokey15,
384               @fokey16, @refkey1, @refkey2, @refkey3, @refkey4, @refkey5, @refkey6,
385               @refkey7, @refkey8, @refkey9, @refkey10, @refkey11, @refkey12,
386               @refkey13, @refkey14, @refkey15, @refkey16
387       end
388   
389       close curs_sysreferences
390       deallocate cursor curs_sysreferences
391   
392       /*
393       ** Everything is now in the result table #fkey_res, so go ahead
394       ** and select from the table now.
395       */
396   
397   SelectClause:
398       select PK_TABLE_CATALOG,
399           PK_TABLE_SCHEMA,
400           PK_TABLE_NAME,
401           PK_COLUMN_NAME,
402           PK_COLUMN_GUID = convert(varchar(36), null),
403           PK_COLUMN_PROP_ID = convert(int, null),
404           FK_TABLE_CATALOG,
405           FK_TABLE_SCHEMA,
406           FK_TABLE_NAME,
407           FK_COLUMN_NAME,
408           FK_COLUMN_GUID = convert(varchar(36), null),
409           FK_COLUMN_PROP_ID = convert(int, null),
410           ORDINAL,
411           UPDATE_RULE,
412           DELETE_RULE,
413           FK_NAME,
414           PK_NAME,
415           DEFERRABILITY
416       from #ofkey_res fkey
417       order by FK_TABLE_CATALOG, FK_TABLE_SCHEMA, FK_TABLE_NAME
418   
419   
420       if (@startedInTransaction = 1)
421           rollback transaction oledb_keep_temptable_tx
422   
423   


exec sp_procxmode 'sp_oledb_fkeys', 'AnyMode'
go

Grant Execute on sp_oledb_fkeys to public
go
RESULT SETS
sp_oledb_fkeys_rset_001

DEFECTS
 QCSC 4 Costly 'select count()', use 'exists()' 146
 QCSC 4 Costly 'select count()', use 'exists()' 211
 QCSC 4 Costly 'select count()', use 'exists()' 254
 QCSC 4 Costly 'select count()', use 'exists()' 255
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
183
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
249
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysreferences.csysreferences clustered
(tableid, frgndbname)
Intersection: {frgndbname}
284
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 372
 TNOU 4 Table with no unique index sybsystemprocs..sysreferences sybsystemprocs..sysreferences
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 136
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 149
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 158
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 174
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 181
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 201
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 214
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 223
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 240
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 247
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 371
 MGTP 3 Grant to public sybsystemprocs..sp_oledb_fkeys  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysreferences  
 MNER 3 No Error Check should check @@error after insert 135
 MNER 3 No Error Check should check @@error after insert 148
 MNER 3 No Error Check should check @@error after insert 157
 MNER 3 No Error Check should check @@error after insert 173
 MNER 3 No Error Check should check @@error after insert 180
 MNER 3 No Error Check should check @@error after insert 200
 MNER 3 No Error Check should check @@error after insert 213
 MNER 3 No Error Check should check @@error after insert 222
 MNER 3 No Error Check should check @@error after insert 239
 MNER 3 No Error Check should check @@error after insert 246
 MNER 3 No Error Check should check @@error after delete 308
 MNER 3 No Error Check should check @@error after insert 309
 MNER 3 No Error Check should check @@error after insert 310
 MNER 3 No Error Check should check @@error after insert 311
 MNER 3 No Error Check should check @@error after insert 312
 MNER 3 No Error Check should check @@error after insert 313
 MNER 3 No Error Check should check @@error after insert 314
 MNER 3 No Error Check should check @@error after insert 315
 MNER 3 No Error Check should check @@error after insert 316
 MNER 3 No Error Check should check @@error after insert 317
 MNER 3 No Error Check should check @@error after insert 318
 MNER 3 No Error Check should check @@error after insert 319
 MNER 3 No Error Check should check @@error after insert 320
 MNER 3 No Error Check should check @@error after insert 321
 MNER 3 No Error Check should check @@error after insert 322
 MNER 3 No Error Check should check @@error after insert 323
 MNER 3 No Error Check should check @@error after insert 324
 MNER 3 No Error Check should check @@error after delete 331
 MNER 3 No Error Check should check @@error after insert 332
 MNER 3 No Error Check should check @@error after insert 333
 MNER 3 No Error Check should check @@error after insert 334
 MNER 3 No Error Check should check @@error after insert 335
 MNER 3 No Error Check should check @@error after insert 336
 MNER 3 No Error Check should check @@error after insert 337
 MNER 3 No Error Check should check @@error after insert 338
 MNER 3 No Error Check should check @@error after insert 339
 MNER 3 No Error Check should check @@error after insert 340
 MNER 3 No Error Check should check @@error after insert 341
 MNER 3 No Error Check should check @@error after insert 342
 MNER 3 No Error Check should check @@error after insert 343
 MNER 3 No Error Check should check @@error after insert 344
 MNER 3 No Error Check should check @@error after insert 345
 MNER 3 No Error Check should check @@error after insert 346
 MNER 3 No Error Check should check @@error after insert 347
 MNER 3 No Error Check should check @@error after insert 357
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 47
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 232
 MUCO 3 Useless Code Useless Brackets 254
 MUCO 3 Useless Code Useless Brackets 296
 MUCO 3 Useless Code Useless Brackets 355
 MUCO 3 Useless Code Useless Brackets 420
 MUIN 3 Column created using implicit nullability 59
 MUIN 3 Column created using implicit nullability 117
 MUIN 3 Column created using implicit nullability 118
 MUIN 3 Column created using implicit nullability 261
 MUIN 3 Column created using implicit nullability 262
 QISO 3 Set isolation level 53
 QJWT 3 Join or Sarg Without Index on temp table 280
 QJWT 3 Join or Sarg Without Index on temp table 282
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {id}
371
 VNRD 3 Variable is not read @ordpkey 191
 VUNU 3 Variable is not used @status 32
 VUNU 3 Variable is not used @msg 33
 VUNU 3 Variable is not used @msg2 34
 CRDO 2 Read Only Cursor Marker (has for read only clause) 273
 MRST 2 Result Set Marker 398
 MSUB 2 Subquery Marker 359
 MSUB 2 Subquery Marker 361
 MSUB 2 Subquery Marker 364
 MSUB 2 Subquery Marker 366
 MSUB 2 Subquery Marker 371
 MTR1 2 Metrics: Comments Ratio Comments: 22% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 27 = 26dec - 1exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 240 17

DATA PROPAGATION detailed
ColumnWritten To
@fktable_qualifiersp_oledb_fkeys_rset_001.FK_TABLE_CATALOG
@pktable_qualifiersp_oledb_fkeys_rset_001.PK_TABLE_CATALOG

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_oledb_fkeys_rset_001 
read_writes table tempdb..#ofid (1) 
reads table sybsystemprocs..sysobjects  
read_writes table tempdb..#opid (1) 
read_writes table tempdb..#ofkey_res (1) 
reads table sybsystemprocs..sysreferences  
read_writes table tempdb..#ofkeys (1) 
reads table sybsystemprocs..sysindexes  
read_writes table tempdb..#opkeys (1)