How can I get temp table meta data as I need to know column names and types. select * from tempdb.sys.syscolumns where tname like '#task' is not working, however #task table is there

asked 10 Aug '12, 10:18

memphis's gravatar image

memphis
16113
accept rate: 0%

1

What type of temporary table is this, how is it created?
- CREATE GLOBAL TEMPORARY TABLE
- CREATE LOCAL TEMPORARY TABLE
- DECLARE LOCAL TEMPORARY TABLE
- SELECT ... INTO #task FROM ...
Which version of SQL Anywhere are you using?

(10 Aug '12, 11:02) Reimer Pods

Table was created using create LOCAL

(14 Aug '12, 07:21) memphis

Are you using SQL Anywhere at all? AFAIK, the particular "tempdb" database is a particular ASE/MS SQL Server facility to store temporary tables.

SQL Anywhere (SA) has no explicit "tempdb" database, nor does it use the three-part-name schema (database.owner.table). So it seems the

select from tempdb.sys.syscolumns

completely ignores the "database" specifier and is really just a

select from sys.syscolumns

and simply returns alls rows for all permanent tables and for all global temporary tables.

So, if you're using SA, then "#task" will only be in the result set if it is a global temporary table.


FWIW, SA's "temp" dbspace is something completely different...

permanent link

answered 10 Aug '12, 11:18

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

Creating global temp table works for me as I can get data from sys.syscolumns. Thanks a lot.

(14 Aug '12, 07:11) memphis

For local temporary tables you may use "select * from sa_describe_query('select * from temp1')". For global temporary table you can pick up the schema from the catalog (e.g syscolumns view).

permanent link

answered 13 Aug '12, 07:14

Hartmut%20Branz's gravatar image

Hartmut Branz
37629
accept rate: 0%

edited 13 Aug '12, 07:14

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×19

question asked: 10 Aug '12, 10:18

question was seen: 1,752 times

last updated: 14 Aug '12, 07:21