Most of you, who are working on T-SQL must be knowing that using “SELECT * FROM <<Table>>” syntax is treated as a Bad Practice and instead, using “SELECT <<Column Names>> FROM <<Table>>” syntax is recommended as a Best Practice for various reasons (discussing those is not in scope of this article).
So, to be able to follow the Best Practice, it requires typing in the names of individual columns which is a time-consuming process especially if there are huge number of columns to be specified in the Select List. To overcome this very concern, there is actually a really cool shortcut for specifying the names of the columns in the SELECT statement with just few mouse clicks. Here is how this simple trick works.
I had previously published a tip titled Quickest way to SELECT All Columns from a Table at Just Learned on BeyondRelational.com. It became very popular and I received a great feedback from the community members. In the interest of my blog’s subscribers and visitors, I am going to present that tip here with a couple of visuals as follows.
Say you want to select all the columns from a table, for which you would write a query something like this:
Now, as specified above, we need to replace the asterisk (*) in the above query with the list of all the columns present in DimProduct table. To do this, follow these steps:
- Go to SSMS, Open a new query window, Type SELECT followed by a space on First Line and FROM dbo.DimProduct on the Second Line.
- Go to Object Explorer | Database Containing DimProduct Table | DimProduct Table
- Expand the DimProduct table and click on Columns folder
- Hold the Left Mouse Button and Drag the Columns folder on to the Query Window after SELECT, which will pull all the columns from DimProduct table and place them after the SELECT statement in the query window separated by commas (,) as shown below.
When you drag the Columns folder, All the Columns from DimProduct are placed after the SELECT Clause and before the FROM Clause in the above query window as shown below.
Now, you can make slight formatting changes to suit your needs and if you want, you can even remove some of the columns based on your need (Remember that, removal is always easier than addition when it comes to Coding and hence this approach!).
This is one of my all time favorite tips and I use this almost all the times :-)
Do let me know, what is your approach of Selecting Columns from a SQL Server Table in a T-SQL Query by leaving a comment below.
Take a look at the other Tips and Tricks in this Series on Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills.
[Updated : 2012-06-12]: One of my friends, Samuel Vanga (Blog | Twitter), pointed me to a Microsoft Connect item “[SSMS] Improvements required for dragging columns from Object Explorer“, on another thread, submitted by a well-known SSIS expert, Jamie Thomson (Blog | Twitter). Please do vote for this connect item if you like this tip and the suggestions made by Jamie Thomson out there on Microsoft Connect.
- Tips ‘N’ Tricks – SSMS – Re-arranging the Column Sequence in the Results Pane (dattatreysindol.com)
- Tips ‘N’ Tricks – SSMS – Execute Single Query against Multiple Servers (dattatreysindol.com)
- Tips ‘N’ Tricks – SSMS – Easy way to Learn Keyboard Shortcuts by Enabling ScreenTips (dattatreysindol.com)
- Tips ‘N’ Tricks – SSMS – Save Your Frequently Used Servers and Databases (dattatreysindol.com)
- Tips ‘N’ Tricks – SSMS – Commonly Used Keyboard Shortcuts in SQL Server Management Studio (dattatreysindol.com)