DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_primarykey  31 Aug 14Defects Dependencies

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


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}
80
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 81
 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' 84
 MUCO 3 Useless Code Useless Brackets 29
 MUCO 3 Useless Code Useless Brackets 36
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 90
 QISO 3 Set isolation level 41
 QNAJ 3 Not using ANSI Inner Join 76
 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]
78
 VUNU 3 Variable is not used @table_qualifier 18
 VUNU 3 Variable is not used @keycnt 20
 VUNU 3 Variable is not used @indexid 21
 VUNU 3 Variable is not used @indexname 22
 VUNU 3 Variable is not used @i 23
 MRST 2 Result Set Marker 67
 MTR1 2 Metrics: Comments Ratio Comments: 16% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 9dec - 1exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 53 15
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 67

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