THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

SQL Server Object Explorer (10 days of SSDT - Day 6)

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server databases nearly every day since then. In that time I’ve learnt a lot about how to make best use of the tool and I want to share that experience in a new series of blog posts called “10 days of SSDT”. I shall be publishing a different blog post every day for 10 days, each day revealing something that you may not know about SSDT. I hope you enjoy the series and contribute via the comments!

SQL Server Object Explorer (SSOX) is a crucial feature of SSDT and is a window that sits within SSDT’s Visual Studio shell; it is made visible from from the View menu or by using the keyboard shortcut CTRL+\, CTRL+S.

image

As a SQL Server developer that chooses to use SSDT I spend most of my time within SSOX. Think if it as a gateway to both your offline database source code and your physical deployed databases.

In Day 1 – Online and offline I said

SSDT supports two distinct ways of working, online and offline. Those two distinct usage scenarios are most obvious with SQL Server Object Explorer

This is demonstrated by the existence of two top-level nodes within SSOX, they are called:

  • SQL Server
  • Projects

You can see both in the screenshot above. The SQL Server node is a list of SQL Server instances that you have registered within SSOX (in the screenshot above I have registered one instance called “(localdb)\Projects”) which you can then expand thus allowing you to browse all the databases and server level objects upon that instance; an easy way to think of this SQL Server node is as a hybrid of the Registered Servers and Object Explorer windows in SQL Server Management Studio (SSMS). You can register any edition of SQL Server (Express through to Enterprise) and also Windows Azure SQL Database (WASD) instances too.

The Projects node of SSMS provides a logical view over SSDT projects that you have open within the Solution Explorer window, I personally am a big fan of SSDT projects (see Day 3 – What is a Projects) so this Projects node is where I spend most of my time. The screenshot below depicts the interaction between Solution Explorer and SSOX:

image

Notice there is a project in Solution Explorer called Day04 – Table Designer and there is a node under Projects within SSOX of the same name (highlighted by the red arrow). That project has two files called Product.table.sql & Product.PrimaryKey.sql, both of which are open in the centre of the screen (note that SSDT does not stipulate that filenames must reflect the name of the object defined within them, but it is useful if they do). Notice also that table [dbo].[Product] and its primary key both appear in SSOX (depicted with the blue lines) however in there they appear as you might expect them to appear in SSMS. They are displayed in a hierarchical view of the database along with appropriate icons to signify the object type (table, primary key, etc…). This is the power of the Projects node in SSOX, it provides a logical view of the objects defined in the files of the project. If you are so inclined you can even define all of your objects within a single file in Solution Explorer (note I’m not recommending you do that) and SSOX will still show the same logical view over them all.

Displaying a logical view of your databases and database projects is the basic premise of SSOX but it does have a number of other features too. For example there is a button on the SSOX toolbar labelled Group by Schema:

SNAGHTML768d76e

This changes the logical view to make Schemas a high-level node from which you can drill in to find all your database objects contained therein:

SNAGHTML765169d6

If you have a lot of schemas in your database then this can be really useful (I just wish they had an option to hide all the built-in schemas such as db_accessadmin, db_backupoperator etc…). A nice side-effect of grouping by schema is that you can right-click on one of the nodes underneath a schema in order to create a new object and the DDL script for that object will place it into the respective schema rather than the default [dbo].

SSOX has a few other features that can be launched from the right-click menu but I’ll leave investigation of those as an exercise for the reader; we have covered the main features of SSOX herein, if you have any questions please feel free to ask them in the comments section below.

@Jamiet

If you want to learn more about SSDT then come along to my training course Introduction to SQL Server Data Tools in association with Technitrain on 23rd/24th February 2015 in London. There is a discount if you register before the end of 2014.

Earlier posts in this series:

Published Wednesday, September 24, 2014 9:06 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Michael Barrett said:

Hi Jamie

Great blog series... Actually, there is - at least in the version of SSDT that I am using (11.1.40706.0) - a way to hide the built-in objects from SSOX. The "Show results" button (which in my version is a "Filter" image) has the option to not "Show built-in objects".

/Michael

September 24, 2014 6:07 AM
 

jamiet said:

Hi Michael,

You're right. I guess when I wrote this (which was a long time ago :) ) I got confused between having that option available for projects but not for instances under the "SQL Server" node.

Thanks for the heads-up.

JT

September 24, 2014 6:15 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 25, 2014 3:36 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 26, 2014 2:40 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

October 1, 2014 2:37 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

October 13, 2014 2:57 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement