Press "Enter" to skip to content

Category: SQL Server Management Studio

Poor SQL Formatter’s SSMS Plugin

Published 2020-04-24 by Kevin Feasel

Dave Bland shows us the Poor SQL formatter for SQL Server Management Studio:

So what is PoorSQL Formatter?  Well, as you probably know, formatting is very important when writing T-SQL code.  While we try to format our code as we write it, sometimes the code we need to review was written by someone else and maybe doesn’t follow formatting standards. This is where PoorSQL Formatter will come in handy.  It will help us take the poorly written code and format it in a manner that is more in line with T-SQL formatting best practices.  The best part, it will do the formatting in seconds.

I’ve used that website many a time. It’s not as detailed as I want it to be, but if you just need a quick and dirty way to format a query so that it’s understandable, this does the trick.

Comments closed

Find and Replace Database Objects in SSMS

Published 2020-04-15 by Kevin Feasel

Daniel Hutmacher has a fun trick for us:

Here’s a quick tip that touches on one of the powerful SSMS tricks in my “Management Studio Level-Up” presentation. Say you have a potentially large number of database objects (procedures, functions, views, what have you), and you need to make a search-and-replace kind of change to all of those objects.

You could, of course, put the database in source control and use a proper IDE to replace everything, then check your code back into source control and commit it to the database. That’s obviously the grown-up solution. Thanks for reading this post.

But let’s say for the sake of argument that you haven’t put your database in version control. What’s the lazy option here?

Even if you do have everything in source control, the first part is still quite useful just in case there’s schema drift.

Comments closed

Installing SSMS on Servers Running SQL Server?

Published 2020-04-15 by Kevin Feasel

Andy Mallon says yes, install SQL Server Management Studio on those servers running SQL Server instances:

“But wait, Andy. That’s not a best practice!” you say?

The pseudo best practice of “don’t install SSMS” is a misguided one–advice that I even fell into repeating in the past. However, that’s actually proposed solution to a best practice, rather than being itself a best practice.

I agree with Andy wholeheartedly on this.

Comments closed

Generating Scripts as a Notebook in SSMS 18.5

Published 2020-04-09 by Kevin Feasel

Emanuele Meazzo is excited about some new functionality in SQL Server Management Studio 18.5:

Microsoft just dropped SSMS 18.5 after almost 5 long months without any updates; this new release fixes a lot of bugs and introduces a few new features, above them all I’m now showing you the following.

I’m sure that you used the the “Generate Scripts” feature in SSMS quite a few times, you could generate the code for schema and/or data for any (or all) the objects in your DB, especially if you haven’t embraced that sweet, elusive, devops workflow.

Well, good news! Other than file, clipboard and the good ‘o new query window, you can now export directly to a new Notebook.

Read on to see what you have to do and what the output looks like.

Comments closed

SQL Server Management Studio 18.5 GA

Published 2020-04-08 by Kevin Feasel

Dinakar Nethi announces that SSMS 18.5 is now generally available:

Today, we’re sharing the release of SQL Server Management Studio (SSMS) 18.5. We have some feature updates as well as important behind the scenes updates.

You can download SQL Server Management Studio 18.5  today and review SSMS Release Notes for full details.

Hugo Kornelis recommends that you update as soon as possible:

And I need all of you to update your version. Now. Yes, right now. Here’s a link to download it. I’ll wait.

Why the rush, you ask? Because hidden in between all the little (and some big) improvements and fixes, there is one true gem. One I wish Microsoft had done … oh, let’s say two decades ago?

Click through to see what has Hugo so excited.

Comments closed

Exploring the Extended Events Live Data Window

Published 2020-03-18 by Kevin Feasel

Grant Fritchey takes us through the Extended Events Live Data window:

One reason a lot of people don’t like Extended Events is because the output is in XML. Let’s face it, XML is a pain in the bottom. However, there are a bunch of ways around dealing with the XML data. The first, and easiest, is to ignore it completely and use the Live Data window built into SQL Server Management Studio.

I’ve written about the Live Data window before, and I’ve been using it throughout this series of posts on Extended Events. There’s a lot more to this tool than is immediately apparent. Today, we’re going to explore the basics around this tool

Read on to see what you can do with this. It’s a lot more powerful than it first appears to be.

Comments closed

Azure Data Studio and Query Store

Published 2020-03-10 by Kevin Feasel

Rob Farley explains why Azure Data Studio can’t be a 100% thing right now:

But the big thing with this new laptop is that I’ve made a conscious decision about what to install on it. And particularly, what things to NOT install.

For example, I’ve only installed SQL inside docker, not directly on Windows. I’m running the Linux version just because I can.

But today I feel like I’ve compromised.

Today I’ve installed SSMS, instead of persisting with just Azure Data Studio. It only took a week for me to cave, and the reason is Query Store.

Read on to understand why. I ended up having to cave on Docker-only for SQL Server because of PolyBase components that I needed and which aren’t (yet?) supported in the Linux version.

Comments closed

Resizing the SSMS Grid Results

Published 2020-03-05 by Kevin Feasel

Kenneth Fisher shows how you can resize SQL Server Management Studio’s results grid:

After my post last month about configuring your tools I’ve been on a make my life easier kick and one of the things that gives me grief every day is that my eyesight is at best mediocre and some days it’s worse than others. (I have issues. No, not just those issues, issues that effect my eye sight.) I’m in SSMS 90% of my day or more so it really helps that it’s easy to zoom in and out on the query window, and even the message results pane. There is a little box with a percentage in the bottom left corner of each of the two panes. This controls the font size of that pane.

This is also helpful if you ever present using SSMS and don’t have a strong grasp of Zoom-It or the magnifier app.

Comments closed

Monitoring Availability Groups

Published 2020-02-25 by Kevin Feasel

Nisarg Upadhyay gives us some of the low-down on monitoring availability groups:

In my previous articles, I have explained the step-by-step process of deploying an AlwaysOn Availability group on SQL Server 2017. In this article, I am going to explain how to monitor AlwaysOn availability groups.

First, let’s review the configuration of the availability group we had deployed previously. To do that, open SQL Server Management Studio  Expand database engine from the object explorer  Expand “AlwaysOn High Availability”  Expand “Availability Groups.” You can see the availability group named SQLAAG. Under this availability group (SQLAAG), you can see the list of availability replicas, availability databases, and availability group listeners.

Click through for some tooling built into SQL Server Management Studio, as well as relevant Perfmon counters.

Comments closed

SSMS Tips

Published 2020-02-13 by Kevin Feasel

Taiob Ali has a few tips for us around using SQL Server Management Studio:

Opening SSMS as a different user

I have two Active Directory Accounts. Once I use to login to my workstation and everything except working inside Databases. For that, I needed to open the SQL Server Management Studio (SSMS) with my admin account. I created an instance of runas application on my desktop to do that. Saves me a few clicks and type in my user name and password every time I launch SSMS. Text of the target.

Click through to see how that works. This is quite useful when you’re working in multiple environments or across multiple domains, especially if you slap on the /netonly parameter.

Comments closed