1 /** SECTION END: CLEANUP **/
2
3
4 CREATE PROCEDURE sp_jdbc_getschemas_cts
5 as
6 declare @schemaname varchar(32)
7 declare @startedInTransaction bit
8
9 if @@trancount = 0
10 begin
11 set chained off
12 end
13 /* check if we're in a transaction, before we try any select statements */
14 if (@@trancount > 0)
15 select @startedInTransaction = 1
16 else
17 select @startedInTransaction = 0
18
19 set transaction isolation level 1
20
21 if (@startedInTransaction = 1)
22 save transaction jdbc_keep_temptables_from_tx
23
24 /* this will make sure that all rows are sent even if
25 ** the client "set rowcount" is differect
26 */
27
28 set rowcount 0
29
30 create table #tmpschemas
31 (TABLE_SCHEM varchar(32) null)
32
33 DECLARE jcurs_getschemas CURSOR
34 FOR
35 select name from sysusers where suid >= - 2
36 FOR READ ONLY
37 OPEN jcurs_getschemas
38 FETCH jcurs_getschemas INTO @schemaname
39 while (@@sqlstatus = 0)
40 begin
41 insert into #tmpschemas values (@schemaname)
42 FETCH jcurs_getschemas INTO @schemaname
43 end
44
45 close jcurs_getschemas
46 deallocate cursor jcurs_getschemas
47
48 select TABLE_SCHEM from #tmpschemas order by TABLE_SCHEM
49 drop table #tmpschemas
50 if (@startedInTransaction = 1)
51 rollback transaction jdbc_keep_temptables_from_tx
52
exec sp_procxmode 'sp_jdbc_getschemas_cts', 'AnyMode'
go
Grant Execute on sp_jdbc_getschemas_cts to public
go