Is there a way to expand the column list in a SELECT * from #Temp_Table in SSMS?

Question

As seen here LINK you can hover over the * in a SELECT * from... and a tooltip will come up with a list of the columns represented by that *.

Is there a way to copy the text from the tooltip to the clipboard?

I know that you can use the columns folder in the object explorer to generate a list of columns by dragging it into the query editor window, but this cannot work for temp tables as they do not appear in the object explorer.

EDIT

For completeness sake, the answer below "You can right-click on the table in Object Explorer and select Script Table as->CREATE to->New Query Editor Window." is also not workable for the same reason as above.

END EDIT

I do not see a way to do this in SSMSBoost, which is an add-on for SSMS that I use. (The only reason I mention it is I am pretty sure I have seen the SSMSBoost developers on SO, feature request??)

EDIT 2: Is there any other way to quickly expand the * in a SELECT * from #Temp_Table without actually running the code to create the temp table? END EDIT 2

Have you tried just pressing CTRL+C when that tooltip is focused? — Mike Perrenoud, Jan 08 '14 at 15:19
SSMSBoost developer here ;) Currently following solution will work: you perform select * from temp table, right click the results grid and select SSMSBoost command "copy all headers". Let me know if it suits you. But anyway I will estimate your proposal and think about even more efficient solution for your requirement. — Andrei Rantsevich, Feb 17 '14 at 23:35
@Andrei HA, that is exactly what I have been doing for the last couple of days and it has certainly sped things up, however, some limitations there are 1)that the table has to exist, which can be tedious. 2) it does not use the table alias or bracket column headers with spaces or dashes. — Daniel E., Feb 18 '14 at 00:39
Correct. Alias issue can be probably solved. But how do you expect the application to "predict" columns in non-existing temp table. Is it declared in currently-opened script ? — Andrei Rantsevich, Feb 19 '14 at 01:30
@AndreiRantsevich, SSMS already does know the columns of any `select *` that has the table defined, if you hover over the `*` it shows you a full list of all columns in the tables named in the from clause, whether or not they are temp tables. — Daniel E., Feb 19 '14 at 15:03

score 13 · Answer 1 · edited Feb 06 '17 at 05:46

13

You can just drag the column's subfolder from the object explorer into the editor and the columns will be added as a list.

edited Feb 06 '17 at 05:46

Alex Wiese

8,142
6
42
71

answered Feb 06 '17 at 03:03

Nader

139
1
4

2

Not doable on temp tables, which is very specifically what the question is about. – Daniel E. Feb 06 '17 at 11:58
1

Did not answer the question at all. There is no folder to drag for CTE and temp tables. – Scotch Apr 15 '21 at 21:55

score 2 · Answer 2 · edited May 23 '17 at 11:47

2

Very similar to this question. Here is the accepted answer from that question:

select * from tempdb.sys.columns where object_id =
object_id('tempdb..#mytemptable');

edited May 23 '17 at 11:47

Community

1
1

answered Jan 08 '14 at 16:42

wergeld

14,332
8
51
81

1

It is similar, however this requires the code to be run and the table actually created, that tooltip populates using Intellisense (I assume) and does not require the table to actually exist. I would like to know if there is a way to do it on the fly... – Daniel E. Jan 08 '14 at 16:56
Interesting. I see what you mean. I mad a script with both a temp table and a table variable but did not "create" them. Hovering over the `*` showed the column names/types/sizes. – wergeld Jan 08 '14 at 17:07

score 2 · Answer 3 · answered Dec 13 '22 at 15:33

2

If you have RedGate SQL Prompt, you can expand wildcards using Ctrl + B, Ctrl + W

answered Dec 13 '22 at 15:33

Lauren Oldja

612
4
11

score 0 · Answer 4 · answered Jan 08 '14 at 15:35

0

You can right-click on the table in Object Explorer and select Script Table as->CREATE to->New Query Editor Window.

The information you want in near the top of the script this command creates.

answered Jan 08 '14 at 15:35

lbarreira

175
10

2

Not doable on temp tables, which the OP has indicated they're interested in. – Damien_The_Unbeliever Jan 08 '14 at 15:41

score 0 · Answer 5 · edited Sep 23 '21 at 00:34

0

The way I usually do this is by highlighting the table name and hitting Alt + F1. Then, you can copy-paste the Column_name values.

edited Sep 23 '21 at 00:34

Jeremy Caney

7,102
69
48
77

answered Sep 22 '21 at 23:08

user3885999

1
1

The object '[#tmp]' does not exist in database 'Test' or is invalid for this operation. This still doesnt work on temp tables. – Daniel E. Oct 07 '21 at 01:13

Is there a way to expand the column list in a SELECT * from #Temp_Table in SSMS?

5 Answers5

Linked

Related