Database	Proc	Application	Created	Links
sybsystemprocs	sp_odbc_primarykey		31 Aug 14	Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*
4     ** note: there is one raiserror message: 18040
5     **
6     ** messages for "sp_odbc_primarykey"               18039, 18040
7     **
8     ** 17461, "Object does not exist in this database."
9     ** 18039, "table qualifier must be name of current database."
10    ** 18040, "catalog procedure %1! can not be run in a transaction.", sp_odbc_primarykey
11    **
12    */
13    
14    create procedure sp_odbc_primarykey
15        @table_name varchar(771),
16        @table_owner varchar(32) = null,
17        @table_qualifier varchar(32) = null
18    as
19        declare @keycnt smallint
20        declare @indexid smallint
21        declare @indexname varchar(771)
22        declare @i int
23        declare @id int
24        declare @uid int
25        declare @startedInTransaction bit
26        if (@@trancount > 0)
27            select @startedInTransaction = 1
28        else
29            select @startedInTransaction = 0
30    
31    
32        select @id = NULL
33    
34    
35        set nocount on
36    
37        if (@@trancount = 0)
38        begin
39            set chained off
40        end
41    
42        if (@startedInTransaction = 1)
43            save transaction odbc_keep_temptable_tx
44    
45    
46    
47        set transaction isolation level 1
48    
49        if @table_qualifier is not null
50        begin
51            if db_name() != @table_qualifier
52            begin
53                /* if qualifier doesn't match current database */
54                /* "table qualifier must be name of current database"*/
55                raiserror 18039
56                return (1)
57            end
58        end
59    
60        if @table_owner is null
61        begin
62            select @id = id, @uid = uid
63            from sysobjects
64            where name = @table_name
65                and uid = user_id()
66            if (@id is null)
67            begin
68                select @id = id, @uid = uid
69                from sysobjects
70                where name = @table_name
71                    and uid = 1
72            end
73        end
74        else
75        begin
76            select @id = id, @uid = uid
77            from sysobjects
78            where name = @table_name and uid = user_id(@table_owner)
79        end
80    
81        if (@id is null)
82        begin
83            /* 17461, "Object does not exist in this database." */
84            raiserror 17461
85            return (1)
86        end
87    
88        create table #pkeys(
89            TABLE_CAT varchar(32),
90            TABLE_SCHEM varchar(32),
91            TABLE_NAME varchar(255),
92            COLUMN_NAME varchar(255),
93            KEY_SEQ smallint,
94            PK_NAME varchar(255))
95    
96    
97        /*
98        **  now we search for primary key (only declarative) constraints
99        **  There is only one primary key per table.
100       */
101   
102       select @keycnt = keycnt, @indexid = indid, @indexname = name
103       from sysindexes
104       where id = @id
105           and indid > 0 /* make sure it is an index */
106           and status2 & 2 = 2 /* make sure it is a declarative constr */
107           and status & 2048 = 2048 /* make sure it is a primary key */
108   
109       /*
110       **  For non-clustered indexes, keycnt as returned from sysindexes is one
111       **  greater than the actual key count. So we need to reduce it by one to
112       **  get the actual number of keys.
113       */
114   
115       if (@indexid >= 2)
116       begin
117           select @keycnt = @keycnt - 1
118       end
119   
120       select @i = 1
121   
122       while @i <= @keycnt
123       begin
124           insert into #pkeys values
125           (db_name(), user_name(@uid), @table_name,
126               index_col(@table_name, @indexid, @i, @uid), @i, @indexname)
127           select @i = @i + 1
128       end
129   
130       select TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, KEY_SEQ, PK_NAME
131       from #pkeys
132       order by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, KEY_SEQ
133   
134       drop table #pkeys
135       if (@startedInTransaction = 1)
136           rollback transaction odbc_keep_temptable_tx
137   
138       return (0)
139   

exec sp_procxmode 'sp_odbc_primarykey', 'AnyMode'
go

Grant Execute on sp_odbc_primarykey to public
go

DEFECTS
MTYP 4 Assignment type mismatch TABLE_NAME: varchar(255) = varchar(771)	125
MTYP 4 Assignment type mismatch KEY_SEQ: smallint = int	126
MTYP 4 Assignment type mismatch PK_NAME: varchar(255) = varchar(771)	126
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	105
MGTP 3 Grant to public sybsystemprocs..sp_odbc_primarykey
MGTP 3 Grant to public sybsystemprocs..sysindexes
MGTP 3 Grant to public sybsystemprocs..sysobjects
MNER 3 No Error Check should check @@error after insert	124
MUCO 3 Useless Code Useless Brackets	26
MUCO 3 Useless Code Useless Brackets	37
MUCO 3 Useless Code Useless Brackets	42
MUCO 3 Useless Code Useless Brackets	56
MUCO 3 Useless Code Useless Brackets	66
MUCO 3 Useless Code Useless Brackets	81
MUCO 3 Useless Code Useless Brackets	85
MUCO 3 Useless Code Useless Brackets	115
MUCO 3 Useless Code Useless Brackets	135
MUCO 3 Useless Code Useless Brackets	138
MUIN 3 Column created using implicit nullability	88
QISO 3 Set isolation level	47
MRST 2 Result Set Marker	130
MTR1 2 Metrics: Comments Ratio Comments: 27%	14
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 16 = 17dec - 3exi + 2	14
MTR3 2 Metrics: Query Complexity Complexity: 71	14

DEPENDENCIES

PROCS AND TABLES USED reads table sybsystemprocs..sysindexes   reads table sybsystemprocs..sysobjects   read_writes table tempdb..#pkeys (1)