 |
|
SQL Server Management Studio Query Designer
|
By: Jeremy Kadlec | Read Comments (11) | Related Tips: More > 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?
- Open SQL Server Management Studio.
- Select the database you want to query from in the drop down list of the SQL Editor tool bar, in our case 'BaltSSUG'.
- Navigate to the Query menu and select the 'Design Query in Editor...' option. Alternatively, press Ctrl+Shift+Q.
- 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?
- Select the user defined tables needed for the query. System tables are not available as options in this tool.
- 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.
- 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.
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.
Next Steps
- The next time you are having a hard time pulling your query together, consider the Query Designer. Many developers and DBAs are visual and this tool serves them well to get started and stay productive.
- As you begin to work with developers and DBAs new to SQL Server, let them know about the Query Designer and show them how it can help them get off the ground.
- Consider the Query Designer in addition to the visual data modeling tools in SQL Server Management Studio when you are starting to work with a new system or just can not remember the data model on a large system.
- Based on initial research, the Query Designer is available from the SQL Server Express to Enterprise editions.
- Check out these related tips on MSSQLTips.com:
Last Update: 1/23/2013
About the author
View all my tips
PrintComments and Feedback:
| Wednesday, August 11, 2010 - 4:57:15 PM - Gary Flatness | Read The Tip |
|
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 |
|
| Tuesday, January 22, 2013 - 1:04:26 PM - Krumeg | Read The Tip |
|
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, January 23, 2013 - 11:37:14 AM - Jeremy Kadlec | Read The Tip |
|
Krumeg, No - Nothing that I am aware of with this tool. Sorry. Thank you, |
|
| Wednesday, January 23, 2013 - 2:01:59 PM - Jeremy Kadlec | Read The Tip |
|
Everyone, This tip has been updated based on feedback from the community. Thank you, |
|
| Monday, February 04, 2013 - 5:41:11 AM - Sarmad | Read The Tip |
|
Hi, Is Query Designer helpful in case we have multiple databases? e.g. database1..Table1 and database2..Table2? Thanks |
|
| Monday, February 04, 2013 - 10:47:25 AM - Jeremy Kadlec | Read The Tip |
|
Sarmad, I think all of the object references are to a single database. Thank you, |
|
| Monday, February 11, 2013 - 9:04:58 AM - Scott Coleman | Read The Tip |
|
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. |
|
| Saturday, April 06, 2013 - 7:38:42 PM - Susan R. | Read The Tip |
|
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, April 08, 2013 - 10:17:54 AM - Jeremy Kadlec | Read The Tip |
|
Susan, No - Not that I am aware of. Thank you, |
|
| Friday, May 24, 2013 - 11:16:28 AM - Ralph Wilson | Read The Tip |
|
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:
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. |
|
| Wednesday, June 05, 2013 - 7:23:21 AM - Royce Roy | Read The Tip |
|
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 |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |