Ssms-Tips-Tricks

SQL Server Management Studio provides a large amount of information about databases by letting the user navigate around Object Explorer and view the properties of objects.

What I’m often surprized by though, is the number of users who haven’t ever explored the reports that are available. In another section, we’ll look at creating custom reports, but there is a wonderful set of built-in standard reports that you should explore. For example, if I right-click the WideWorldImporters database, I can see these reports:

2025-05-29

In early versions of SQL Server, the only way to try to track dependencies between tables, procedures, functions, etc. was to use the sp_depends stored procedure.

And everyone thought it lied.

The real problem was that it didn’t understand partial dependencies and deferred resolution of objects. For example, it got confused if you created a procedure that mentioned a table, then later created the table.

SQL Server 2012 introduced far superior dependency views, and SQL Server Management Studio (SSMS) shows dependencies using those views under the covers.

2025-05-27

I don’t use the table designer in SQL Server Management Studio. Sorry, but I just don’t like it, or the options that it chooses for me. I’d rather use T-SQL every time, but I’m also the first to admit that there are plenty of people who would use that designer.

And when they do, many run into an error that says:

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

2025-05-25

Over the years, I’ve had a surprising number of questions on how to output all the data in a table as a series of INSERT statements. SQL Server Management Studio has had the ability to do this for a long time. Here’s an example.

In Object Explorer, I’ve expanded the WideWorldImporters database, then expanded Tables. Where people come unstuck is they right-click the table, and look at the scripting options:

2025-05-23

I’ve mentioned a number of times how useful I think the Object Explorer Details panel is in SQL Server Management Studio.

One option in that panel that might not be so obvious is the sorted navigation. Here’s an example.

I’ve opened the WideWorldImporters database in Object Explorer, and clicked on the Tables node:

I then hit F7 to open the Object Explorer Details pane and clicked the Name heading to sort the table list. I like it ascending so you might need to click it twice:

2025-05-21

If you are working with databases with large numbers of objects, the contents of Object Explorer in SQL Server Management Studio can start to become a bit overwhelming. I wish it offered an option to group by schema. That would be helpful.

But you can at least filter by things like schema, when you need to work with a specific set of objects. You’ll notice that if you click on the database name, that the filter in the toolbar is grayed out, but if you click on a node below that like Tables, you can click on the toolbar filter icon. You can also right-click the node and choose to filter:

2025-05-19

I started working with SQL Server in 1992, but all through the 1980’s and 1990’s, I was also working with Progress 4GL. I thought it was the best of the character-based 4GLs but unfortunately, they did a poor job of migrating to Windows and we decided to stop using the product.

One thing that I used to love with Progress though is that the metadata for each column in the database was much richer than what is present in SQL Server. In fact, Microsoft Access was probably closer to it in that regard. It’s something I really missed when moving to SQL Server. In Progress, when I defined a column, I could also define things like:

2025-05-17

I’ve mentioned in an earlier article about scripting multiple objects at once, how useful the Object Explorer Details window is, and how little understood it is.

Another useful option in it, is that the displayed columns can be changed. In particular, you can add columns that would be useful. Let’s look at an example.

In Object Explorer, I’ve expanded the WideWorldImporters database and clicked on the word Tables:

Next, I hit the F7 key, and the Object Explorer Details pane opens showing this:

2025-05-13

If you want to script all the indexes on a table (or all the tables, all the stored procedures, etc, etc.), you can do that the long way by using the Generate Scripts right-click option for a database, but there’s a better way.

Let’s use the Purchasing.PurchaseOrderLines table from WideWorldImporters as an example. Here are the indexes on it:

The scripting options are well-known. You right-click the object, and can navigate to the scripting option:

2025-05-11

Previously I showed how useful it is to be able to drag columns from either a table or from user-defined table type onto a query window.

But one of the first comments I hear from people who did that is:

I hate all those square brackets. How do I stop that?

The option to do that was added to Tools> Options > SQL Server Object Explorer > Commands:

Bonus points for the team because they also provided us with an option to decide whether schema names are dragged as well. That’s mostly used for tables but also applies to other schema-bound objects like stored procedures.

2025-05-09