Tips ‘N’ Tricks – SSMS – View the Column List, Data Types, and Null Option in Query Tooltips

While writing T-SQL queries in SQL Server Management Studio (SSMS), we often need to type the names of the columns in the SELECT statement (and in fact this is a best practice as well against using “SELECT * “). Sometimes we may not remember the name of a particular column in a Table/View or the name of a column in the returned result set of a Table-valued Function etc. Following are some of the options:

  • Go to Object Explorer, Expand the Corresponding Database, Expand the Corresponding Table/View to see the exact Column Names.
  • Go to Object Explorer, Expand the Corresponding Database, Expand the Corresponding Table/View and drag the Columns folder on to the query window which bring the list of all the columns into your query window separated by commas as explained in this article: Quickest way to SELECT All Columns from a Table

However, using thee above two approaches, we cannot get the list of columns that will be present in the result set returned by a Table-Valued Function. To address this issue and also to see the column names quickly, here is a really easy and cool way.

Say you have typed:

SELECT * FROM <<Object Name>>

Now, hover your mouse over the asterisk/star symbol (*) and you will see the following details in the Tooltip:

  • List of Columns
  • Data Type of Columns
  • Null Option of Columns

This approach works for the following type of objects (So far I have tried only these objects, and this might work for other type of objects as well):

  • Tables
  • Views
  • Functions

Below screenshots show the information contained in the Tooltip on the asterisk/star symbol of your SELECT query for different types of objects.

Tooltip shows Column Name, Data Type, and Null Option etc. for a Table

 

Tooltip shows Column Name, Data Type, and Null Option etc. for a View

 

Tooltip shows Column Name, Data Type, and Null Option etc. for a Table-Valued Function

Have you ever tried using this feature? Do you find this feature interesting/useful? Do let me know by leaving a comment below.

Note: The above demonstration is based on the SQL Server 2008 R2 SSMS installed on Windows XP.

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.

Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on June 7, 2012, in Shortcuts, SQL Client Tools, SQL Server, SSMS, SSMS Tips, T-SQL, T-SQL Tips, Tips 'N' Tricks and tagged , , , , , , , , , . Bookmark the permalink. 5 Comments.

  1. Given that SSMS knows the columns even for a temp table, and that it is horrible practice to leave select * anywhere, it is kind of weird that you cannot quickly expand SELECT * into a full column list!! I do like the feature though, thanks!

  2. i use SQLserver 2008 R2. Tried this feature many times. It never works. The best i can get in tool-tip is server and db name the table belongs to.

  3. Tim Buterbaugh

    Is there any way to get the column sizing info as well? For example “last_name varchar(50), not null” would be much more useful.

  1. Pingback: Tips ‘N’ Tricks – Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills « Datta's Ramblings on Business Intelligence 'N' Life

  2. Pingback: Tips ‘N’ Tricks – SSMS – Quickest way to SELECT and Query All Columns from a Table in SQL Server « Datta's Ramblings on Business Intelligence 'N' Life

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: