DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_primarykey  14 déc. 14Defects Propagation Dependencies

1     
2     
3     
4     /*--------------------Primary Key-----------------------------*/
5     
6     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
7     /*
8     ** note: there is one raiserror message: 18040
9     **
10    ** messages for "sp_oledb_primarykey"               18039, 18040
11    **
12    ** 17461, "Object does not exist in this database."
13    ** 18039, "table qualifier must be name of current database."
14    ** 18040, "catalog procedure %1! can not be run in a transaction.", sp_oledb_primarykey
15    **
16    */
17    
18    create or replace procedure sp_oledb_primarykey
19        @table_name varchar(771) = null,
20        @table_owner varchar(32) = null,
21        @table_qualifier varchar(32) = null
22    as
23        declare @keycnt smallint
24        declare @indexid smallint
25        declare @indexname varchar(771)
26        declare @i int
27        declare @id int
28        declare @uid int
29        select @id = NULL
30        declare @startedInTransaction bit
31    
32        if (@@trancount > 0)
33            select @startedInTransaction = 1
34        else
35            select @startedInTransaction = 0
36    
37        set nocount on
38    
39        if (@@trancount = 0)
40        begin
41            set chained off
42        end
43    
44        set transaction isolation level 1
45    
46        if (@startedInTransaction = 1)
47            save transaction oledb_keep_temptable_tx
48    
49        if @table_owner is null
50        begin
51            select @id = id, @uid = uid
52            from sysobjects
53            where name = @table_name
54                and uid = user_id()
55            if (@id is null)
56            begin
57                select @id = id, @uid = uid
58                from sysobjects
59                where name = @table_name
60                    and uid = 1
61            end
62        end
63        else
64        begin
65            select @id = id, @uid = uid
66            from sysobjects
67            where name = @table_name and uid = user_id(@table_owner)
68        end
69    
70        select
71            TABLE_CATALOG = db_name(),
72            TABLE_SCHEMA = user_name(@uid),
73            TABLE_NAME = @table_name,
74            COLUMN_NAME = index_col(@table_name, i.indid, c.colid, @uid),
75            COLUMN_GUID = convert(varchar(30), null),
76            COLUMN_PROPID = convert(int, null),
77            ORDINAL = c.colid,
78            PK_NAME = i.name
79        from sysobjects o, sysindexes i, syscolumns c
80        where o.id = i.id
81            and c.id = i.id
82            and i.id = @id
83            and o.uid = @uid
84            and i.indid > 0
85            and i.status2 & 2 = 2
86            and i.status & 2048 = 2048
87            and index_col(@table_name, i.indid, c.colid, @uid) != null
88        order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
89    
90        if (@startedInTransaction = 1)
91            rollback transaction oledb_keep_temptable_tx
92    
93        return (0)
94    


exec sp_procxmode 'sp_oledb_primarykey', 'AnyMode'
go

Grant Execute on sp_oledb_primarykey to public
go
RESULT SETS
sp_oledb_primarykey_rset_001

DEFECTS
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {uid}
83
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 84
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 51
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 57
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 65
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 80
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 80
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 81
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 81
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 82
 MGTP 3 Grant to public sybsystemprocs..sp_oledb_primarykey  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNAC 3 Not using ANSI 'is null' 87
 MUCO 3 Useless Code Useless Brackets 32
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 93
 QISO 3 Set isolation level 44
 QNAJ 3 Not using ANSI Inner Join 79
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
Uncovered: [number, colid]
81
 VUNU 3 Variable is not used @keycnt 23
 VUNU 3 Variable is not used @indexid 24
 VUNU 3 Variable is not used @indexname 25
 VUNU 3 Variable is not used @i 26
 MRST 2 Result Set Marker 70
 MTR1 2 Metrics: Comments Ratio Comments: 18% 18
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 10dec - 1exi + 2 18
 MTR3 2 Metrics: Query Complexity Complexity: 53 18
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 70

DATA PROPAGATION detailed
ColumnWritten To
@table_namesp_oledb_primarykey_rset_001.TABLE_NAME °.COLUMN_NAME

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysindexes  
reads table sybsystemprocs..syscolumns  
writes table sybsystemprocs..sp_oledb_primarykey_rset_001 
reads table sybsystemprocs..sysobjects