13

Write this: 

    Select      *         From tablea

Want this:

    Select c1, c2, c3 ... From tablea

SQL Server Management Studio provides intellisense to pick one column at a time. It takes a while for table with lots of columns.

I can use Script Table as =>Select To=> New Query Edit Window, and the copy/paste to my original script. however is there a less interruptive way?

I write SQL a lots, thanks for help!

Rm558
  • 4,621
  • 3
  • 38
  • 43
  • The native functionality does not exist but there are tools like RedGate's SQL Prompt that are able to do it. – billinkc Oct 03 '14 at 00:41

4 Answers4

24

Found this myself.

  1. Type Select * From TableA in SSMS.
  2. Exactly Select text Select * From TableA.
  3. Use Ctrl+Shift+Q to pop up Query Designer window.
  4. Click OK. "*" is replaced by "c1, c2, c3..."

Query Designer auto-populates all the columns. just open it & close. if you want only portion of columns, I found it is still easier to delete them on text editor than trying to use mouse to uncheck them on GUI.

Still hope to find a way to eliminate the Keyboard/mouse switch on step 4? seems there is no short-cut key for the OK button.

Update 2019-06-24

Above method does not work for table in SQL Azure. ApexSQL SQL formatter seems working well for me, it's free.

enter image description here

Update 2020-10-30

found a similar function in LINQPad, it allows me to auto-complete all properties in LINQ, See here

enter image description here

Rm558
  • 4,621
  • 3
  • 38
  • 43
  • This is the best answer I found because it works for temporary tables and other intermediate results (sub-selects, etc.). It appears to run off the same machinery that Intellisense does and does not require the tables to exist on their own. – Brian Diggs Jul 19 '18 at 18:33
  • If you add an alias to your table ALL of the fields are populated without the alias. Not a deal breaker but still... now I have to go in and copy/paste the alias to each field. – Code Novice Mar 17 '20 at 20:40
6

In SQL Server Management Studio if you expand the object explorer and then the node for the particular table in question then you can drag the columns node into the query window and this will type out all of the column names for you.

There are probably better ways to do this, but that's what I use.

Paul McLoughlin
  • 2,283
  • 1
  • 15
  • 15
1

Totally understand this answer is not specific to SSMS but in VSCode via the mssql extension:

Write this:

Select *
From TableX

Place cursor after the * and press Ctrl+Spacebar and option to insert all columns appears

whytheq
  • 34,466
  • 65
  • 172
  • 267
-1

If your object explorer is open then use drag & drop feature.

e.g. Northwind

Step 1: Expand the database by single click on plus icon or just double click on "Northwind"
Step 2: Expand tables "Tables"

Now you will see "Columns", "Keys", "Constraints" etc

Click on "Columns" to drag to your editor window.

You can try for other as well like "Keys", "Constraints".

But for "Indexes", did not work for me.

Hope you will find useful tips.

Anjum Rizwi
  • 1
  • 3