Sybase stored procs which use temp tables. Creation issue

I am trying to create a sybase stored procedure which references a temp table, but I do not know the definition of开发者_开发知识库 this temp table. I could look at the proc and figure out what the temp table looks like and manually create it first, but I'm faced with approximately 1000 procs (which reference all sorts of temp tables) and this solution would be extremely tedious.

I've been looking for a more strategic approach but no luck so far. I'd appreciate it if you could share your thoughts with me.

It appears you may not understand that the whole point of a #table is that:

1. it is temporary

2. it exists only in the context of the stored proc that creates it

3. it is private

Therefore creating a new stored proc to "reference" the #table of another proc, is not a reasonable thing to attempt. Either write a completely independent stored proc with its own #table, or change the original stored proc so that the temporary table exists outside its context (see below).

You will have to jump through hoops, and different hoops for different versions of Sybase, to get at either the definition or the data in such #tables.

• If you can catch the moment when one of those stored procs is executing, and you have sa privilege, you can certainly examine the DDL via SybaseCentral or other DBA tool.

If you are performing a documentation exercise, then there is no alternative to examining the sproc code; if you do not, you will miss important aspects of the #table that is buried in the code.

For temporary tables that are intended to be shared (ie. exist outside the context of a stored proc), instead of:
CREATE TABLE #my_table ...
use:
CREATE TABLE tempdb..my_table ... and execute that outside any proc, before compiling the procs.

I think you could get #table metadata from tempdb systables and syscolumns tables.

Look at Rob Verschoor's article http://www.sypron.nl/temptab.html about #table name and tempdb dump load trick.