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


exec sp_procxmode 'sp_odbc_primarykey', 'AnyMode'
go

Grant Execute on sp_odbc_primarykey to public
go
RESULT SETS
sp_odbc_primarykey_rset_001

DEFECTS
 MTYP 4 Assignment type mismatch TABLE_NAME: varchar(255) = varchar(771) 126
 MTYP 4 Assignment type mismatch KEY_SEQ: smallint = int 127
 MTYP 4 Assignment type mismatch PK_NAME: varchar(255) = varchar(771) 127
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 106
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 63
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 69
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 77
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 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 125
 MUCO 3 Useless Code Useless Brackets 27
 MUCO 3 Useless Code Useless Brackets 38
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 139
 MUIN 3 Column created using implicit nullability 89
 QISO 3 Set isolation level 48
 MRST 2 Result Set Marker 131
 MTR1 2 Metrics: Comments Ratio Comments: 27% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 18dec - 3exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 71 15

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

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