The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.3k287438644
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:

×18

question asked: 10 Aug '12, 10:18

question was seen: 1,693 times

last updated: 14 Aug '12, 07:21