Lecture 8-- Tour the SSMS interface
- [Instructor] Management Studio, or SSMS, will serve as the main interface when you're working with SQL Server. After logging into the server, using my Windows Authentication Account, we'll see this very basic interface that I have on my screen now. Across the top of the screen is a group of icons that collectively make up something called the Standard Toolbar. If we visit the View menu and then come down to Toolbars, we'll see that we have over a dozen different toolbars that can be activated. And right now, the Standard toolbar is the only one that's on. Some toolbars will activate automatically as you perform tasks within Management Studio.
For instance, I'll go ahead and click off of the menu somewhere to dismiss it, and then press the New Query button up here on the top. This will open up a new window, ready for us to type in a transact SQL command. You'll also notice that we have an additional row of tools now, which includes a dropdown menu for the database that we want to make active. We have a button to execute the query that we've written, as well as some debugging options. If we revisit the View menu and come down to toolbars again, we'll see that the new toolbar that was just added, is called the SQL Editor toolbar. If we go ahead and click off of this, and then close the SQL window by pressing on the X in its upper right hand corner of its tab, we'll see that that toolbar now disappears from the screen.
On the left side of the screen is something called the Object Explorer. This gives us easy access to all of the components that are currently live within our instance of SQL server. At the top is a line that displays the instance name, the version number of SQL Server that we're currently working with, as well as the currently logged in user account. Below that, we have a collection of databases and security items, as well as several others. Each of these are folders that can be opened up to see their content. Simply press the plus icon to the left of each folder. Inside databases, for example, we can see a folder for System Databases, and a folder for Database Snapshots.
Let's go ahead and expand the System Databases folder, and we'll see the four system databases of master, model, msdb and tempdb. These are the same system databases that we saw during the installation movie. If we keep drilling down into the master database, by expanding its folder, we'll see that it's made up of Tables, Views, Synonyms and so on. Going deeper, we can see that a View, for instance, is made up of a couple of different objects. Let's go into this first System View called dbo.spt_values, and we'll see that it's made up of Columns, Triggers, Indexes and Statistics.
And finally, the Columns collection of this particular view includes the names and details of the individual columns that make up the structure of the tabular data contained within. So this chain can get pretty deep. If you need to, you can go ahead and use the scroll bar on the right hand side of the Object Explorer window, as well as make the entire window wider by clicking on its edge and dragging it open a little bit further. That'll eat into the main workspace on the screen, though. So if you need more room on your screen to work, find this push pin icon on the top of the menu. When you click it, it'll turn on the AutoHide option, where the Object Explorer reduces down to a single tab when it's not in use.
To get it back, simply click on the tab. You can always click on the push pin again to lock the window open. You can also close the Object Explorer window altogether, by clicking on the X icon in the upper right. To get it back, go ahead and visit the View menu, and you'll turn it on with the first option here at the top. Now most people work with the Object Explorer on the left hand side of the screen, but you have flexibility here if you'd like. Go ahead and click and drag on the title bar, and we can move it into a new position. And you'll see that these drop areas light up. If you drag and drop it on one of these, it'll lock it into a new location.
Let's go ahead and drag and drop it back on the left side of the screen. When working with Management Studio, you'll also find that a lot of functionality can be found within the right click contextual menus. For instance, if we return our attention to this view inside of the master database, called dbo.spt_values, if we right click on it we'll see a bunch of different options. The first option is to view some of the data within by choosing Select Top 1000 Rows. This'll open up a new window and display both the command that was executed at the top, as well as the results down here at the bottom.
In this case, the first 1000 rows from this view of our data. This is another instance of the query window, and you'll notice that our SQL editor toolbar has once again returned to the top of the screen here. We can alter the command that was issued at the top of this window, as it's essentially just a simple text editor window. For instance, if I only want to review the name and number columns, I can remove the other references by going ahead and highlighting them, and then pressing the Delete key on my keyboard. Now if I re-execute this command, we'll see the new results down below only include the name and number columns.
On the SQL Editor toolbar, one of the options we have is to control where the results of our output gets sent to. Right now with this button activated, we can see that it's returning the results to a grid, which is what we're seeing down here in the bottom. We also have the option to export our results to text, as well as export them out to a file. In addition to the results tab down on the bottom, there's also a tab where we can receive any messages that might have been generated by the system here. In this case, I just see that it was successfully executed and produced the expected results of returning 1000 rows. In some cases, you'll see error messages here which'll be helpful for troubleshooting.
The other common thing that you'll find yourself needing to do a lot in SQL Server Management Studio, is refresh the contents that are being displayed. We saw this when we looked at the System Administrator account details. By right clicking on an object, for instance I'll right click on the System Views object, we can choose the Refresh option. This will force the system to update icons and data that you'll see on the screen, to reflect any changes that might have been made to the server. So those are the basics of navigating around within SQL Server Management Studio. You'll use the Object Explorer to find objects that you want to interact with, and using the right click menu, perform some common tasks.
By opening up the Query window, you can retrieve records from the database, and perform other activities. And all along the way, Management Studio will activate additional toolbars that'll contain functions that pertain to your current task.
▶️ DTube
▶️ IPFS