Press "Enter" to skip to content

Category: Tools

Useful Operations in dbatools

Rod Edwards shows off some nice functionality in dbatools:

I often build solutions around the dbatools functions, the below is just some of my Operational favourites. With some I’ve included the output pipe that I use most frequently, but obviously, you can view and use the output however you choose to. Clearly, DBATools has many functions to add/remove/update SQL as well, but i’m just folking on the ‘gets’ here.

Naturally, as mentioned…its powershell, you can programmatically use this for any of your automation needs. Marvellous.

The ever growing list of commands can be found here: command index – dbatools . This can prove daunting to new users of the toolset, so here’s a starter for 10.

Click through for those 10.

Comments closed

Tokenizing SQL Queries

Phil Factor has a new script:

Imagine this: you have several directories full of SQL script files, and you need to know where a certain table is used. You’d rather like the context too, so you can check the whole SQL Expression and work out why it is running so slowly. Maybe, from that same daunting set of several directories, you need to search for a comment, either end of line, or block comment, perhaps structured. It could be that you just need to execute each query or statement in turn to check performance.

It’s not unusual to want to search just within strings. Although simple searches can be effective, you will at some point need a tool that is able to recognise and return a collection of strings representing the SQL code, divided up into the respective components of the SQL language.

For this article, we’ll use a PowerShell cmdlet, called Tokenize-SQLString, which is in my GitHub repository.

Read on for examples of how it works and the types of things you can do with it.

Comments closed

Script Comparison with kdiff3

Steve Jones is speaking my language:

I had a customer recently ask if SQL Compare could show them the differences in two scripts they’ve written. They weren’t using version control (tsk, tsk, shame), but saw SQL Compare and the “Scripts folder” option. This isn’t used for random scripts, but I do have a better solution: KDiff3.

KDiff is an old project that is used to analyze multiple files and merge the differences. There is an archived SoundForge location, but the more modern version is here. That’s the current code location, and you can see the readme for details. To get started, download and install it.

I remember (cue “Pepperidge Farms Remembers” meme) back when kdiff3 was only available in KDE. That’s when I first learned of it, and ever since there was a Windows port, I’ve been a dedicated user. Yes, it’s an old tool, but it works really well.

Comments closed

SQL Compare at the Command Line

Steve Jones is resting his mouse-clicking finger:

Recently a customer was looking to automate some of their SQL Compare checks, but they wanted to do this in a dynamic way, since they needed to do this at scale. Their idea was to not have a SQL Compare project, but build a mapping at the command line.

This post addresses a part of their issue. This will look at how to set up a basic SQL Compare command line.

Read on to see how. This is something I did a while back as well, though it was quite a while ago (say, 8-9 years ago) and required us to do some DLL weirdness back then. Once we got it working, however, it did a good job.

Comments closed

New Release: Power BI VS Code Extension

Gerhard Brueckl has been busy:

I am working a lot with Power BI in my daily business and there have always been a couple of things that bothered me since the very beginning. Most of this is related to the web UI and its usability, mainly that you need too many clicks to get to where you want (e.g. viewing Datasets refreshes) but also that some features are simply not exposed in the UI that are possible with the Power BI REST APIs (e.g. rebinding a report to another dataset). So I thought there must be some better way to do this and make management and usability of Power BI easier and I came up with the idea for a Visual Studio Code extension for Power BI to close this gap.

Read on to see how it works, how you can get it, and what Gerhard has in mind for it over the short term.

Comments closed

Automating SQL Dump Analysis

Sean Gallardy has been busy:

One of the things that is lacking in the community is for some simple dump analysis for SQL Server, well no longer!

Introducing the Beta version of SQL Dump Analysis… sure it’s not super pretty, sure it’s not 100% yet… but it’ll give you the basic goods and get you on your way.

This is a website you upload SQL dumps to, rather than an executable you’d download. Still, check it out.

Comments closed

Deadlock Analysis with SQL Sentry Plan Explorer

Rod Edwards looks at deadlocks:

I’ve also included a REALLY useful bit of functionality in a common free tool that you may not expect. Simply scroll to the to the end for it if you’re up to speed with Deadlocks already, or don’t want to read my inane rambles.

So we know what a deadlock is, multiple queries all contending for resources, one query holds some resources and is waiting on resources that another query has. That second query isn’t giving the resources up and is waiting for resources that the first query has. Both are stubborn, neither are releasing what they have until they completed…so we have a standoff.

Read on to learn a bit more about the mechanics behind deadlocks, as well as how Plan Explorer makes life easier.

Comments closed