Press "Enter" to skip to content

Category: SQL Server Management Studio

SQL Tools Updates

Published 2022-03-01 by Kevin Feasel

Timi Oshin has updates on SSMS and Azure Data Studio:

Azure Data Studio 1.35 now supports easier keyboard navigation in notebooks without mouse clicking. This is done by hitting the Esc key and navigating between cell rows using the Up and Down arrow keys. To enter edit mode, hit the Enter key on the keyboard. The new Table Designer preview feature supports creating new tables and editing existing tables on a connected SQL Server instance. This is a highly requested product enhancement and enables more productive schema management with a modern, streamlined UX.

Haha! It only took several years but my hectoring finally pays off. Now for the full set of Jupyter keyboard shortcuts…

Comments closed

SQL Server’s Central Management Server

Published 2022-02-22 by Kevin Feasel

Lee Markup builds a SQL Server inventory:

Today, we will be looking at using T-SQL and a Central Management Server to create a SQL Server inventory. Let’s say that you’re  new at this company or in this role and all you have right now is the list of SQL Servers that people know about. you haven’t been able to run the MAP Toolkit or maybe you’ve been told that you can’t run it for some reason. the list of SQL Servers that people “just know about” probably isn’t anywhere near complete, but you have to start somewhere.

These things are a life-saver, especially once you have more than a couple of instances to worry about.

Comments closed

Version Control for SSMS Templates

Published 2022-02-18 by Kevin Feasel

Kevin Chant saves some templates:

Previously I wrote a post about how to do version control for SQL Server Management Studio templates using Azure Repos. I wanted to highlight some things I did not point out in that post. In addition, I thought it was only fair that I showed how to do it with GitHub.

Plus, in my last T-SQL Tuesday post I mentioned the SQL Server diagnostic queries provided by Glenn Berry. Which reminded me to do this post. Because I want to do an example based on sharing one of the queries with your colleagues via GitHub. Like in the below diagram.

Click through to see the process.

Comments closed

Fun with the SSMS Extended Events UI

Published 2022-02-16 by Kevin Feasel

Grant Fritchey airs a few grievances:

I like Extended Events and I regularly use the Session Properties window to create and explore sessions. I’m in the window all the time, noting it’s quirks & odd behaviors, even as it helps me get stuff done. However, found a new one. Let me tell you about just a few of them.

Click through for some examples of UI oddities when working with session properties.

Comments closed

Finding Looked-Up Columns on Queries

Published 2022-01-25 by Kevin Feasel

Grant Fritchey searches for included columns:

A common issue in performance is the lookup, key or RID, but the question frequently asked is, which columns are looked up? You need to know in order to try to address the issue. The answer is easy enough to find. Let’s take a quick look.

Read on for the answer, which you can find in SQL Server Management Studio. Or by shredding a bunch of XML if that’s the kind of thing you’re into.

Comments closed

Building an SSMS Database Solution

Published 2022-01-04 by Kevin Feasel

Andy Leonard has a four-parter four us on database solutions in SQL Server Management Studio. Part one provides an introduction:

I like Microsoft Visual Studio a lot. I know some members of the team that developed Visual Studio, and they are scary-smart individuals who have forgotten more about developing software than I will ever know.

For some reason, I am not fond of SQL Server projects in Visual Studio. I believe the reason is that I am not familiar with the template. Please note I used the word fond intentionally. It’s an emotion. In this case, it’s all about me. I believe my emotion would change if I took the time to learn more about the Visual Studio SQL Server project template.

I continue to attempt to learn VS database projects. In the meantime, I prefer SQL Server Management Studio solutions.

Part two shows how to add a new query:

One solution is to add instrumentation to T-SQL scripts. I personally like to write T-SQL scripts that idempotent (a fancy way to describe “re-executable with the same results”). One way to write idempotent T-SQL is:

1. First check for the current state

2. Provide feedback (instrumentation) on the status

3. Provide more feedback on actions driven by the status (yep, more instrumentation)

Part three includes tables and views in the mix:

Click the “New Query” button in SSMS and add the following T-SQL:

Part four includes stored procedures:

Note the DDL to manage stored procedures is very similar to the DDL for managing views.

If all goes according to plan, the first execution of the s.i DDL T-SQL statement should generate the following messages:

Andy also shows how to use SQLCMD to create a proper deployment script.

Comments closed

Changing Case in SSMS

Published 2021-12-15 by Kevin Feasel

Steve Jones has a quick tip for us:

I never knew I could change case for objects in SSMS easily. This actually was something that another individual pointed out to me, but once I tried it, I liked it and know I’ll use it at times.

Click through to see how to change your code to lower-case or upper-case in a single command.

Comments closed

Page Allocation Reports in SSMS

Published 2021-10-26 by Kevin Feasel

Eitan Blumin has updated an open source project:

Back in April 2020, I created an open-source project called “SQL Server Page Allocation Reports“. It consisted of a set of SQL queries and some Power BI reports that can be used for visualizing the size and locations of your data and transaction log pages.

Well, recently I also added SSMS Custom Reports into the mix. So, it’s time to revisit this project and see what’s new!

Click through to see what’s new.

Comments closed

The Alt Key in SSMS

Published 2021-09-29 by Kevin Feasel

Barney Lawrence shows off vertical selection in SQL Server Management Studio:

I went years without knowing how useful the Alt key was, it’s not well documented but I can guarantee that once you know about it and give it a little practice you won’t be able to live without it.

While I’m filing this under SSMS Tips and Tricks but it works equally in Visual Studio, VS Code, Azure Data Studio and even Notepad ++ (but not plain old vanilla notepad). It’s worth a test in other places too.

As a quick note, it does not work quite the same way in Visual Studio Code or Azure Data Studio as in Visual Studio or SSMS: Alt+Shift+Down arrow copies the current row and pastes it in the row below. Holding Alt and Shift while using the mouse does work, but if you’re big on keyboard commands, you’ll be a bit disappointed.

Comments closed

Toggling Word Wrap in SSMS

Published 2021-09-27 by Kevin Feasel

Ronen Ariely shows how to enable word wrap in SQL Server Management Studio:

Line breaking, also known as word wrapping, is breaking the displayed of a section of text into lines so that the text will fit into the available width of the editor. When writing queries this feature is not so useful as breaking the script line may make the query less readable, but when writing long comments this feature become one of the most useful feature. 

This post simply shows you how to use word-wrap by default or add a command button to Toggle Word Wrap – it’s a built-in feature which is less known and if you did not used it yet, then it is time to use the power of word wrap

Because T-SQL is not line or whitespace sensitive, my preference is to break lines well before they hit the point where word wrap makes sense. But if you’re working with some lengthy lines of code or on a low-resolution laptop, this can help a lot.

Comments closed