| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_jdbc_primarykey | | 31 Aug 14 | Defects Dependencies |
1 /** SECTION END: CLEANUP **/ 2 3 /* 4 ** Altered from the ODBC sp_pkeys defined in sycsp11.sql. 5 ** 6 ** To facilitate eventually combining scripts for ODBC and JDBC, 7 ** only the ordering of the arguments and the final select have been modified. 8 */ 9 /* 10 ** note: there is one raiserror message: 18040 11 ** 12 ** messages for 'sp_jdbc_primarykey' 18039, 18040 13 ** 14 ** 17461, 'Object does not exist in this database.' 15 ** 18039, 'table qualifier must be name of current database.' 16 ** 18040, 'catalog procedure %1! can not be run in a transaction.', sp_jdbc_primarykey 17 ** 18 */ 19 20 create procedure sp_jdbc_primarykey 21 @table_qualifier varchar(32), 22 @table_owner varchar(32), 23 @table_name varchar(300) 24 as 25 declare @msg varchar(255) 26 declare @keycnt smallint 27 declare @indexid smallint 28 declare @indexname varchar(255) 29 declare @i int 30 declare @id int 31 declare @uid smallint 32 declare @actual_table_name varchar(300) 33 declare @startedInTransaction bit 34 35 if (@@trancount = 0) 36 begin 37 set chained off 38 end 39 40 /* see if we're in a transaction, before we try any select statements */ 41 if (@@trancount > 0) 42 select @startedInTransaction = 1 43 else 44 select @startedInTransaction = 0 45 46 /* this will make sure that all rows are sent even if 47 ** the client "set rowcount" is differect 48 */ 49 50 set rowcount 0 51 52 select @actual_table_name = @table_name 53 54 select @id = NULL 55 56 set nocount on 57 58 set transaction isolation level 1 59 60 if (@startedInTransaction = 1) 61 save transaction jdbc_keep_temptables_from_tx 62 63 if @table_qualifier is not null 64 begin 65 if db_name() != @table_qualifier 66 begin 67 /* if qualifier doesn't match current database */ 68 /* 'table qualifier must be name of current database'*/ 69 exec sp_getmessage 18039, @msg output 70 raiserror 18039 @msg 71 return (2) 72 end 73 end 74 75 exec sp_jdbc_escapeliteralforlike @table_name 76 77 if @table_owner is null 78 begin 79 select @table_owner = '%' 80 end 81 82 if (select count(*) from sysobjects 83 where user_name(uid) like @table_owner ESCAPE '\' 84 and ('"' + name + '"' = @table_name or name = @table_name)) = 0 85 begin 86 /* 17461, 'Object does not exist in this database.' */ 87 exec sp_getmessage 17674, @msg output 88 raiserror 17674 @msg 89 return (3) 90 end 91 92 create table #pkeys( 93 TABLE_CAT varchar(32), 94 TABLE_SCHEM varchar(32), 95 TABLE_NAME varchar(255), 96 COLUMN_NAME varchar(255), 97 KEY_SEQ smallint, 98 PK_NAME varchar(255)) 99 100 101 DECLARE jcurs_sysuserobjects CURSOR 102 FOR 103 select id, uid 104 from sysobjects 105 where user_name(uid) like @table_owner ESCAPE '\' 106 and name = @table_name 107 FOR READ ONLY 108 109 OPEN jcurs_sysuserobjects 110 111 FETCH jcurs_sysuserobjects INTO @id, @uid 112 113 while (@@sqlstatus = 0) 114 begin 115 116 /* 117 ** now we search for primary key (only declarative) constraints 118 ** There is only one primary key per table. 119 */ 120 121 select @keycnt = keycnt, @indexid = indid, @indexname = name 122 from sysindexes 123 where id = @id 124 and indid > 0 /* make sure it is an index */ 125 and status2 & 2 = 2 /* make sure it is a declarative constr */ 126 and status & 2048 = 2048 /* make sure it is a primary key */ 127 128 /* 129 ** For non-clustered indexes, keycnt as returned from sysindexes is one 130 ** greater than the actual key count. So we need to reduce it by one to 131 ** get the actual number of keys. 132 */ 133 if (@indexid >= 2) 134 begin 135 select @keycnt = @keycnt - 1 136 end 137 138 select @i = 1 139 140 while @i <= @keycnt 141 begin 142 insert into #pkeys values 143 (db_name(), user_name(@uid), @actual_table_name, 144 index_col(@actual_table_name, @indexid, @i, @uid), @i, @indexname) 145 select @i = @i + 1 146 end 147 148 /* 149 ** Go to the next user/object 150 */ 151 FETCH jcurs_sysuserobjects INTO @id, @uid 152 end 153 154 close jcurs_sysuserobjects 155 deallocate cursor jcurs_sysuserobjects 156 157 /* 158 ** Original ODBC query: 159 ** 160 ** select table_qualifier, table_owner, table_name, column_name, key_seq 161 ** from #pkeys 162 ** order by table_qualifier, table_owner, table_name, key_seq 163 */ 164 /* 165 ** Primary keys are not explicitly named, so name is always null. 166 */ 167 select TABLE_CAT, 168 TABLE_SCHEM, 169 TABLE_NAME, 170 COLUMN_NAME, 171 KEY_SEQ, 172 PK_NAME 173 from #pkeys 174 order by COLUMN_NAME 175 176 drop table #pkeys 177 178 if (@startedInTransaction = 1) 179 rollback transaction jdbc_keep_temptables_from_tx 180 181 return (0) 182
exec sp_procxmode 'sp_jdbc_primarykey', 'AnyMode' go Grant Execute on sp_jdbc_primarykey to public go
| RESULT SETS | |
| sp_jdbc_primarykey_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..sysobjects reads table sybsystemprocs..sysindexes calls proc sybsystemprocs..sp_jdbc_escapeliteralforlike read_writes table tempdb..#pkeys (1) calls proc sybsystemprocs..sp_getmessage  reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1)  reads table master..syslanguages (1) calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) |
