SQL Server Management Studio Query Designer

By:   |   Updated: 2013-01-23   |   Comments (18)   |   Related: > Tools


Problem

When writing SQL Server queries I either need to reference an existing data model or visualize the SQL Sever tables from my database in my head in order to complete my query.  Does a visual query tool exist in the SQL Server Management Studio tool set?  If so, how can I access it and use it to streamline my SQL Server development process?  Check out this tip to learn more.

Solution

The SQL Server Management Studio ships with Query Designer which in a nutshell is a visual querying tool to build SELECT statements.  This is an add-in to the Query Window offering a means to add specific tables from a specific database to the designer.  You can see a visual representation of the tables with the referential integrity all in one screen, then build the needed query with JOINs, WHERE and\or ORDER BY clauses.

How do I access the SQL Server Management Studio Query Designer?

  1. Open SQL Server Management Studio.
  2. Select the database you want to query from in the drop down list of the SQL Editor tool bar, in our case 'BaltSSUG'.
  3. Navigate to the Query menu and select the 'Design Query in Editor...' option.  Alternatively, press Ctrl+Shift+Q.
  4. The Query Designer will load and follow the steps in next section to select the needed tables, then build and test the query.

What are the capabilities with the SQL Server Management Studio Query Designer?

  1. Select the user defined tables needed for the query.  System tables are not available as options in this tool.

SQL Server Management Studio Query Designer Table Selection

  1. Once the tables load in the top of the interface, arrange the tables so they make sense to you.  Next, select the columns that you want to use in the query as well as the alias, sort type, sort order, etc.  Notice how the T-SQL for the SELECT statement is being created and updated as columns are chosen.  Finally, press the 'OK' button and the query will then load into the original query window in SQL Server Management Studio as show in step 3.

SQL Server Management Studio Query Designer organized with tables to build a query

  1. The query as setup in step 2 will load into the original Query Editor window where the query can be executed, tested and modified to meet your needs.

Code from the SQL Server Query Designer is loaded into a SQL Server Management Studio query window for further development and testing

Can this tool help me with INSERT, UPDATE or DELETE statements?

Yes - as a matter of fact.  Just right click in the query pane and select Change Type and then the type of query desired.

SQL Server Management Studio Query Designer Supports SELECT, INSERT, UPDATE and DELETE commands

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2013-01-23

Comments For This Article




Thursday, April 14, 2022 - 12:03:26 PM - Sue Back To Top (90007)
This article was very helpful. I've struggled with a) find the designer in SSMS and then effectively using it. After reading it, I was able to use it immediately to start designing more complex queries. My version of SSMS is 18.7.1 and it works fine with the information you are posting. Thank you.

Wednesday, February 27, 2019 - 12:13:36 AM - Paresh Back To Top (79136)

This tool is no longer available.

Monday, January 11, 2016 - 6:33:50 PM - pardha Back To Top (40399)

Hi,

 

If we want to join tables from multiple databases which one is the best query editor or builder.

Wednesday, August 12, 2015 - 7:52:55 AM - DaveBoltman Back To Top (38430)

@Wonsup Jung - please see http://www.activequerybuilder.com/download.html

- There is one version for Java

- Also Fly Speed query builder is free, and may do what you need

Wednesday, August 12, 2015 - 7:33:27 AM - DaveBoltman Back To Top (38429)

The disadvantage of doing it that way, is that you cannot see the results. Personally I view this a a shortfall in SSMS.

Instead, I right-click on some random table or view and "Edit Top N Rows", then delete the table from the designer. Then I can paste my query into pane 3, or build a new query.

I love that you can press Ctrl-1, 2, 3 or 4 to turn the panes on or off (well not Ctrl-4 in the limited modal query deigner mentioned in this article, but Ctrl-1, 2 and 3 still work).

Monday, February 9, 2015 - 12:02:22 AM - Wonsup Jung Back To Top (36183)

 

To whom it may concern,

 

Hi! ,

 

I am looking for some kind of tool to embed it into my app, which will be the app to remove the personal security information from the large financial database. Because there are various kind of customer and database, My product have to adapt according to the customer environment and made query statement to remove personal security information properly. So, I thought some kind of query making tool would be very helpful.  Followings are my rough questions, it would be helpful if you give me any info of your product, so I would make another detailed question again.

 

1) Can I embed your product into my java app.

 

2) My design concept

 

   a) Call your app. by clicking button, then your app appeared.

 

   b) Your app open some GUI, which support for me to make some query (select, insert, delete, update)

 

   c) Your app display the result in the edit box, which is included in your app.

 

   d) Then I check the result in the edit box, and click the accept button.

 

   e) Then, Your app. copy the result and paste it into the edit box of my app.

 

   f) Then, I close your app by clicking close button, then your app disappeared from my app.

 

 

 

Please let me know if you hard to understand because of weak English explanation.

 

Thursday, July 3, 2014 - 10:40:59 AM - Megan Brooks Back To Top (32533)

I have used the query designer extensively in the past, and it works with a number of different types of queries and with CTEs, "derived tables" and other T-SQL features (though not optimally) if you are using a recent version. I have recently started a new job, however, where the performance demands on the databases are much higher, and I have not opened the query designer once. The main issues are:

  • The resultant code can be weirdly arranged, and hard to fix (it may even re-arrange things back where they were before you fixed it)
  • It sometimes "optimizes" queries to the point of breaking them. Not all of the rearrangements that it applies are valid.
  • It's not useful for complex queries containing embedded comments that you want to keep.
It is still potentially a useful tool for diagramming complex queries, although I find that with practice I really don't need it for that, day to day. The key to using it with derived tables is to select just the derived table query itself and open the query designer against that. You can likewise open it against the query contained in a CTE -- it works against any selection that is a valid T-SQL query (among those CRUD query types that it supports but not, for example, MERGE).

Wednesday, June 5, 2013 - 7:23:21 AM - Royce Roy Back To Top (25303)

This is awesome!! really helpful for me. Thanks for sharing with us. Following links also helped me to complete my task. 

 

http://technet.microsoft.com/en-us/library/ms177264.aspx

 

http://www.mindstick.com/Articles/f0aaa8d8-98be-48c1-b702-c5264b9e0213/?Design%20Query%20in%20Editor%20SQL%20Server%202008%20R2

 

Friday, May 24, 2013 - 11:16:28 AM - Ralph Wilson Back To Top (25130)

This works great for a straight forward SELECT query but it won't work if you have any even mildly advanced features.  For instance, forget about using it with any of the following:

  • CTEs;
  • EXCEPT
  • LEFT OUTER JOIN to a subselect statement
  • IF {NOT} EXISTS . . .

Interestingly enough, it DOES work with an INSERT statement that selects from a CTE . . . as long as you don't try to include the CTE in what you are working with in the Designer area.  Of course, that is only marginally useful.

Monday, April 8, 2013 - 10:17:54 AM - Jeremy Kadlec Back To Top (23231)

Susan,

No - Not that I am aware of.

Thank you,
Jeremy Kadlec
Community Co-Leader

Saturday, April 6, 2013 - 7:38:42 PM - Susan R. Back To Top (23216)

Is there a way to have the "design query" GUI... and the "results" in 2 panes... but in the same 1 window?

 

I have to forever keep closing down the "design query" window... and moving back to the "sql view" and "results" windows separately.

 

Monday, February 11, 2013 - 9:04:58 AM - Scott Coleman Back To Top (22046)

There used to be a "Design Query in Editor..." button on the toolbar in Enterprise Manager and possibly Management Studio 2005, but it is missing in SSMS 2008 and 2012.  The functionality is still there, and you can right-click on the Management Studio tool bar and choose "Customize" if you want to restore it.

You can also select the text of a query before starting the query designer, and it will open with that query as a starting point (assuming it was syntactically correct).  If you know which tables you're looking for in a large database, starting with "SELECT * FROM tablea,tableb,tablec" and then adding the joins in the query designer is easier than picking them from a very long dropdown list.

Monday, February 4, 2013 - 10:47:25 AM - Jeremy Kadlec Back To Top (21898)

Sarmad,

I think all of the object references are to a single database.

Thank you,
Jeremy Kadlec
Community Co-Leader

Monday, February 4, 2013 - 5:41:11 AM - Sarmad Back To Top (21895)

Hi,

Is Query Designer helpful in case we have multiple databases? e.g. database1..Table1 and database2..Table2? Thanks

Wednesday, January 23, 2013 - 2:01:59 PM - Jeremy Kadlec Back To Top (21656)

Everyone,

This tip has been updated based on feedback from the community.

Thank you,
Jeremy Kadlec
Community Co-Leader

Wednesday, January 23, 2013 - 11:37:14 AM - Jeremy Kadlec Back To Top (21653)

Krumeg,

No - Nothing that I am aware of with this tool.  Sorry.

Thank you,
Jeremy Kadlec
Community Co-Leader

Tuesday, January 22, 2013 - 1:04:26 PM - Krumeg Back To Top (21623)

Hi Jeremy,

what if the database consists of a couple of hundred tables but to build your query you just need less than 10 tables. Can you somehow filter the list of tables you can use in the graphical query designer to display only those tables needed?

 

Thanks

Krumeg

Wednesday, August 11, 2010 - 4:57:15 PM - Gary Flatness Back To Top (10040)
Jeremy,

I'm a novice at best on SQL (trial by fire), but found your article to be very helpful with my initial question. 

I saved the query, but when I close the program and reopen (and reopen the query) and execute, it gives an error message (message 208-invalid object name 'table name').  Do I need to rebuild the query each time the program is cycled, or is there something I can do to get it to execute without rebuilding each time.

Thanks and have a good day,

gflat














get free sql tips
agree to terms