Table Column Differences with T-SQL and PowerShell

Where I start off with one idea, than veer widely towards another…much like me normally

As part of my post for the latest T-SQL Tuesday, I talked about community scripts. One of these scripts was by Jana Sattainathan ( blog | twitter ) and it was to do with finding tables with similar table structure. This is a great script for initially finding the tables and something that I am not sure that I would have thought of!

However, do me a favour and in your favourite search engine, do a search for “stackoverflow sql server wide tables” (Google search included for your benefit).

For some reason, people have a slight fascination with wwwwwiiiiiiiiddddeeeee tables!

So thanks to the excellent work done by Jana, you have now identified the tables with similar structure, but what about if we want to know which column names match exactly?

I recently had this problem with consolidating copies of tables of medium to wide length in a database and needed to know. So I created a little snippet of code to help me out and I present it to you, in all of it’s unashamed vulnerability, in the hope that it can help you out.

Be warned: Thar be PIVOTs ahead!

Ahoy, matey!

So we’ve got two tables that are slightly different, not completely different (hey, they could be) but different enough to be similar without matching… (differently similar?)

Our task is to find out which columns are in both tables, which columns are only in 1 table, and which columns are only in the other.

Now this isn’t so bad manually, especially if you only need to do this once, maybe twice. What happens though if you need to do it with multiple pairs of tables? Or multiple pairs of wide tables like our search engines showed us above?

So let us do what all DBAs should do when they have a repetitive, manual task stopping them from doing more fun important things: Automate it away!

Avast Ye!

Our two tables are as follows:

CREATE TABLE dbo.DifferenceTable01
(
    col1 int,
    col2 int,
    col4 int,
    col6 int
);
GO

CREATE TABLE dbo.DifferenceTable02
(
    col2 int,
    col3 int,
    col4 int,
    col5 int
);
GO

Now we can use the sys.columns table to check out the different columns in the table but the results are, well, meh

SELECT 
    OBJECT_NAME([object_id]) AS TableName,
    [name] AS ColumnName
FROM sys.columns 
WHERE [object_id] IN
(
    OBJECT_ID(N'dbo.DifferenceTable01', N'U'),
    OBJECT_ID(N'dbo.DifferenceTable02', N'U')
);
GO
sys_columns_results.PNG
Even if I ordered it, it would still be “meh”…

That is not really going to work out for us…
So I’m not liking the look of this, and going through the results, it seems to me that these results are just not useful. This isn’t the computers fault – it’s done exactly what I’ve told it to do – but a more useful result would be a list of columns and then either a simple ‘Yes’, or a ‘No’.

There’s syntax for this…PIVOT

Thar She Blows!

As anyone who has seen me dance can attest to, I can neither shake, rattle, nor roll. And I definitely do not normally PIVOT. However, as I’m trying to know my tools, I do know that this is the syntax that I need.

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

So after looking up the syntax for this once (ok, 5 times!) I managed to come out with a script that I’m reasonably happy with.

And the results are a lot easier to read 🙂

human_readable_format
Can also be extended to more than 2 tables!

So much better! This way, no matter how long the tables, I can easily figure out what columns are in what table(s) based on their names.

Shiver Me Timbers!

Isn’t it always the way after you’ve done something, you realise a much easier way to do it?

The Old Seadog!

I’ve talked about automation in this post and I have yet to mention PowerShell. I bow my head in shame.

Especially when it has a native command like Compare-Object.

help Compare-Object -ShowWindow

Synopsis
Compares two sets of objects.

Description
The Compare-Object cmdlet compares two sets of objects. One set of objects is the “reference set,” and the other set is the “difference set.”

The result of the comparison indicates whether a property value appeared only in the object from the reference set (indicated by the <= symbol), only in the object from the difference set (indicated by the => symbol) or, if the IncludeEqual parameter is specified, in both objects (indicated by the == symbol).

If the reference set or the difference set is null ($null), this cmdlet generates a terminating error.

So the question we have to ask ourselves now is “Can we do what we did with the PIVOTs easier?”

The Old Salt.

I’ve recently found out about splatting so, of course, I’ve started to use it EVERYWHERE!

Let’s “splat” our two parameters

$Table01Param = @{
ServerInstance = 'localhost\SQLDEV2K14'
Database = 'master'
Query = "SELECT OBJECT_NAME([object_id]) AS TableName, [name] AS ColumnName FROM sys.columns WHERE [object_id] = OBJECT_ID(N'dbo.DifferenceTable01', N'U');"
}

$Table02Param = @{
ServerInstance = 'localhost\SQLDEV2K14'
Database = 'master'
Query = "SELECT OBJECT_NAME([object_id]) AS TableName, [name] AS ColumnName FROM sys.columns WHERE [object_id] = OBJECT_ID(N'dbo.DifferenceTable02', N'U');"
}

And we now save ourselves the trouble of writing the parameters to the functions.

Invoke-Sqlcmd @Table01Param
Invoke-Sqlcmd @Table02Param
splat-attack
SPLAT-ATTACK!!

Since everything is now set up, we can just pass those results into 2 different variable holders and use our Compare-Object.

$Table01 = Invoke-Sqlcmd @Table01Param
$Table02 = Invoke-Sqlcmd @Table02Param

Compare-Object -ReferenceObject $Table01 -DifferenceObject $Table02 -Property ColumnName -IncludeEqual
Non-splat-attack
It’s annoyingly wide without splatting…

And for anyone saying “Yeah, but who knows what ‘SideIndicator’ means!”,  I advise you to read the help with PowerShell. It helps a-lot!

The result of the comparison indicates whether a property value appeared only in the object from the reference set (indicated by the <= symbol), only in the object from the difference set (indicated by the => symbol) or, if the IncludeEqual parameter is specified, in both objects (indicated by the == symbol).

If you are still complaining about it – “You can’t have the HumanReadableFormat column like you did in T-SQL” – then please stop. There are experts out there who can make you go “wow” with what they do. I’m still learning but even I have an answer to that.

Compare-Object -ReferenceObject $Table01 -DifferenceObject $Table02 -Property ColumnName -IncludeEqual |
Select-Object -Property *,@{N='HRF';e={switch ($_.SideIndicator)
{'==' {'Both'}
'=>' {'Table 2 only'}
'<=' {'Table 1 only'}
}
}}
hrf
Go on! Compare this with T-SQL

Land-ho!

I’m not trying to argue with who would win between T-SQL and PowerShell, that was definitely not my intention with this post. T-SQL is my first language love, PowerShell is very quickly becoming my second (and that’s not just because it’s the only other langauge I know).

They both accompolish the same thing!

It’s just a matter of preference, that’s all, not a matter of competition. You want to work in SSMS, PIVOT it up! You like PowerShell, hammer that Compare-Object nail!
Whatever works for you.

My first idea for this was T-SQL but it turns out for me that PowerShell is the easier option. Don’t rule out one, just because the other was the first thing to pop into your head.

Now I’m going to go before I make a joke about Pirates, SQL and the R language…

 

Dealing with System.Data.DataRow.

Words: 1018

Time to read: ~ 5 minutes

Tl;Dr: Make sure you’re calling the property, not just the variable i.e. $Var.ColumnName, not just $Var

Expert Opinion.

I had being sitting on this blog post for a while but then came a recent blog post by Mike Fal ( b | t ) that defended the use of  Invoke-Sqlcmd. Well, it turns out that Mike’s post was in response to Drew Furgiuele’s ( b | t ) blog post condeming it!

If that wasn’t bad enough, I then came across an article by Steven Swenson ( b | t ) that was in response to Mike’s article. Guess what? Another condemnation of  Invoke-Sqlcmd!

It seems that Invoke-Sqlcmd is the Marmite of the PowerShell/SQL Server world. That’s the equivalent of the Crunchy Peanut Butter versus Smooth Peanut Butter debate for my American readers. (Hi Aunt Kate and Uncle Tom!)

Now if you want some real concise, knowledgeable, and professional opinions on the pros and cons of this command, I encourage you to check out those blog posts. I’ve linked to them and I’ve read them all, each with a blend of “oh yeah” and “huh, good point” comments thrown in.

Let’s Get Personal.

The reason that I wanted to throw in my thoughts in this debate is because, as much as I love Mike’s article, it doesn’t deal with the biggest problem that I had with Invoke-Sqlcmd.

Dealing with those stupid, annoying System.Data.DataRow

system-data-datarow
Look at them there…taunting us!

I eventually  figured out how to deal with these and wanted to pass the information on.

The Set Up.

For all those playing along at home, I’ve got a SQL Server 2016 Developer Edition with a copy of WideWorldImporters, as well as PowerShell version 5.

Let’s see how many customesr we have…

SELECT COUNT(*)
FROM Sales.Customers;
CustomerCount
I am not adding 2 more customers, no matter what!

Now I don’t know about you but when I query stuff in a SQL database, it’s to do something to/with the results. They could be a list of servers that I monitor, they could be a list of databases that I want to check the recovery model of, or it could be a list of tables that I want to see how much space they are using. The main point is that I want to do something with the results.

But for this simple case, I just want to list out the customer name from this table. Simple? Yes, but this is just a test case to prove a point.

So let’s PowerShell this!

And so our problems begin.

Now, the basic premise is this:

For each customer name, I just want to output the line “Currently working on” & the customer name.

Now this is based on a real world example where it was a list of servers and I wanted to include this in Write-Debug.

Pain 1.

Invoke-Sqlcmd -ServerInstance localhost -Database WideWorldImporters -Query @"
SELECT CustomerName AS Name
FROM Sales.Customers;
"@ | ForEach-Object {
  "Currently working on $_"
}

Nice and simple PowerShell command, what I would call a “Ronseal” but when we run it…

system-data-datarow
grr!

I’m just going to follow this up with code and pictures of what I tried to do to get this to work…Hopefully you’ll get some amusement out of this…

Pain 2.

In this case I figured maybe I should put the results into a variable first and then see if it could work.

$Employees = Invoke-Sqlcmd -ServerInstance localhost -Database WideWorldImporters -Query @"
SELECT CustomerName AS Name
FROM Sales.Customers;
"@

foreach ($employee in $Employees) {
    "Currently working on $employee"
}
system-data-datarow
Nope!

Pain 3.

Well I know that PowerShell arrays start at 0, and I know that I can get the count of elements in an array by using <variable>.count so maybe that will work?

0..$Employees.Count |
    ForEach-Object {
        [int]$i = $_

        $employeeRange = $Employees[$i]

        "Currently working on $employeeRange"
    }

 

 

0basedArrayNotHighlighted
Nope!

Pain 4.

A quick check on Google points me to using ItemArray with my loops so I try that.

0..$Employees.Count |
    ForEach-Object {
        [int]$i = $_

        $employeeRange = $Employees[$i].ItemArray

        "Currently working on $employeeRange"
    }
0basedArray
YES!!! Wait…what the?

Ahhh! I know that PowerShell is 0 based but I didn’t realize that means the count is going to give me 1 extra row! Plus that’s a bit too much lines for my liking. All that just to output a customer name? Nah let me try again.

Pain 5.

for ($i = 0; $i -lt ($Employees.Count);, $i++) {

    $EmployeeFor = $Employees[$i].ItemArray

    "Currently working on $EmployeeFor"
}
forgood
FORtunately FOR gets me the FORenames (get it?)

The Real Solution.

If only I had run this…

$Employees | Get-Member

You know, there’s a reason that they say the 3 best commands are Get-Help, Get-Command, and Get-Member.

It’s because they save so much time if you just look at them.

$Employees | Get-Member
e_gm
If I may direct your attention to the MemberType of “Property”…

As it turns out there is such an easier way to get the data values back from Invoke-Sqlcmd,

if you want the data, just change $_ to $_.<property>

Let’s see if it works for us.

Pleasure 1.

If we “correct” our original code…

Invoke-Sqlcmd -ServerInstance localhost -Database WideWorldImporters -Query @"
SELECT CustomerName AS Name
FROM Sales.Customers;
"@ | ForEach-Object {
    "Currently working on $($_.Name)"
}

 

forgood
Oh…that’s lovely!

Pleasure 2.

And what about with variables?

foreach ($employee in $Employees.Name) {
"Currently working on $employee"
}
forgood
Brings a tear to my eye, it does 🙂

It looks like we finally have a proper Ronseal moment.

Final Thoughts.

I have absolutely no problem with Invoke-Sqlcmd, so I suppose I fall into Mike’s side of the camp.

Do I use it all the time though? Not really.

The SMO objects have an amazing amount of information that is just too difficult to get with Invoke-Sqlcmd so I’ve started to use the SMO more and more.

But Invoke-Sqlcmd is a tool, just like everything else. There’s no point in throwing away a tool just because it isn’t the most optimal anymore, especially when it is so useful in adhoc situations.

There are some cases where a small handheld screwdriver is more useful than an electric one, just like there are some cases where Invoke-Sqlcmd is more useful than the SMO objects.

Just know your tools…

[PowerShell] Getting More From Generic Error Messages.

There’s more to $error than meets the eye.

What we know already:

SQL Server has some really stupid, generic error messages.
Case in point…

String or binary data would be truncated.

Yes, we know what it means but what column would be truncated? What value would be the offender here?
I am okay with not having the exact answer but it would be nice to have more!

What I learned:

PowerShell actually has some pretty generic error messages as well.
Since I am using PowerShell mainly for interacting with multiple SQL instances, my PowerShell errors mainly revolve around SQL Server.
So this error message is not helpful.

initialerrormessage

(I’m slightly colour-blind so I can barely read red on blue, I find this green (yellow?) easier)

Can we get more?

Sure we can but let’s set up an example so you can play-along at home too.

First of all, what PowerShell version are we using?

$PSVersionTable.PSVersion
psversion
Latest as of…when I updated it

Great! So let us add in our assemblies that will allow us to connect to SQL Server using SMO.

# Load the assembly since we probably do not have it loaded
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')
assembly_loading
This is technically depreciated but I’m not going to remember that whole location…

Now I like the results showing up but if you don’t want them, just throw a $null =  before the [System.Re... bit.

# SILENTLY load the assembly since we probably do not have it loaded
$null = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo')

Now let us connect to mine (or your) database to run some scripts against it.

# Connect to the instance and database
$SQLInstance = New-Object Microsoft.SqlServer.Management.Smo.Server 'localhost'
$Database = New-Object Microsoft.SqlServer.Management.Smo.Database
$Database = $SQLInstance.Databases.Item('Pantheon')

Everything is getting thrown into a variable/object here so there is going to be no output. Just change the ‘localhost’ bit to your server and ‘Pantheon’ to your test database.

Now, let’s get our T-SQL on!

# Create our T-SQL statement.
$sql = 'SELECT SERVERPROPERTY('ProductLevel') AS What?, SERVERPROPERTY('ProductVersion') AS Huh?;'
initialsqlerror
PowerShell & SQL…

You can see the first problem we run into here. The single quotation marks are breaking up our statement.
There are 2 fixes for this; we can double quotation mark the start and end of the string e.g. "SELECT ..." or we can do what we normally do in SQL Server and double up the single quotation marks e.g. (''ProductLevel'').
I’ve gone with the latter but hey, you choose, go crazy, whatever you want!

So now we have this:

initialsqlfix
You can probably already spot the error here from a T-SQL viewpoint…

Now let us run this against our database and see what happens.

# Execute with results...kinda like it says...
$Database.ExecuteWithResults($sql).Tables
initialerrormessage
GENERIC MESSAGE ALERT!

The whole reason for this blog post i.e. stupid, generic error message.

Now ignoring the fact that you already know what is wrong, this tells me that there is either something wrong with the $Database variable, the $sql variable or the syntax statement. Maybe even something else though!
This is not helpful and I’m going to have a bad time.

I encountered this lately and thanks to Chrissy LeMaire ( b | t ), I was introduced to the $error variable.
You can look up what this guy does by running the following on PowerShell,

help about_automatic_variables -showwindow

but the main point is that $error …

Contains an array of error objects that represent the most
recent errors. The most recent error is the first error object in the
array ($Error[0]).

So we want more information about our error message so we go…

$Error[0]

And we get…

initialerrorvariable
…well at least I can read it easier…

the same…
This…this is not what I wanted.
Thankfully, the defintion states that it is an error object and we know that objects can have more properties than what is shown be default.

So we try again, making sure that we return everything

# More than Generic
$Error[0] | Select-Object *
initialerrorvariableall
Great, now “More than a Feeling” is stuck in my head…

Bingo, that is a lot more helpful! Especially when we scan the results and we see this guy (highlighted)

initialerrorvariableallhighlighted
You saw that that was going to be it, right?

We may be working with PowerShell but we still have to obey SQL Server’s rules. So if we want to have a column with a question mark, we’re going to need to wrap it in square brackets.
So let’s fix up our $sql variable and try again.

# fix me!
$sql = 'SELECT SERVERPROPERTY(''ProductLevel'') AS [What?], SERVERPROPERTY(''ProductVersion'') AS [Huh?];'

We re-run out execute…

#Execute with results...kinda like it says...
$Database.ExecuteWithResults($sql).Tables

Lo-and-behold!

results
Those are stupid columns names, to be fair…

Like a sheepdog, let’s round it up:

I’m liking PowerShell more and more as I use it.

That is mainly outside of work but I’ve already turned my gathering of daily checks data from a half hour long process to a 2 minute one.

So it’s nice to know that, while it may have stupid, generic error messages, it also has the tools to help you with them.

Now if we could only get the tools to deal with “String or binary data would be truncated”…

 

SQL Server Configuration Manager: Where has it gone?

Why this blog post?

Now this is a short one.

A while back I was testing attempting to access SQL Server using a defined port number.

To do that, you have to access SQL Server Configuration Manager to specify the port number, or at least I think you do (If you don’t have to do it this way, please, oh please, let me know!).

So, since my laptop is running on Windows10, I open up the Start menu, type in “config” and…nothing!
No SQL Server Configuration Manager! The closest I got was the configuration manager for Reporting Server.

I’m shocked by this especially because when I type it into the Start menu now, I get…

sqlserverconfigurationmanager
Windows 10 making a liar out of me…

Ignoring the fact that it shows up in the result pane now, I had to go to MSDN and figure out where it’s default path is.

But WHY this blog post?

Well it turns out that, for me, the default path is now in…

C:\Windows\sysWOW64\SQLServerManager”<nn>”.msc

I say “<nn>” because it’s a number dependant on what version of SQL Server that you are running.
For example, I have a 2012, a 2014 and a 2016 version on my laptop so I have 3 versions of SQL Server Configuration Manager.

sqlserverconfigurationmanager_we
I may have been msc-taken, get it? 🙂

Seriously, is that it?

Nope, that ain’t it.

Opening up Windows Explorer, going all the way down to that level just to get the config manager? Ain’t nobody got time for that!

They say “imitation is the greatest form of flattery” so taking ideas garnered from dbatools and the fact that I’m just finished a pre-con for SQL Saturday Boston in PowerShell from Mike Fal ( b | t ), thank you Andy Mallon ( b | t ) for pushing me out of my comfort zone even if it was only to just sign up!, I’ve created a little PowerShell script to let me choose and open a SQL Server Configuration Manager.

Check it out! And let me know what you think.

Script me!

Get-ChildItem -Path C:\Windows\sysWOW64\ -Filter "*SQLServerManager*" |
Out-GridView -PassThru |
Invoke-Item

 

scriptme
Seriously “OutGridView -PassThru” should get an award!

Now, I only have 2014 service up on this time so when Out-GridView pops up, I’m going to choose SQLServerManager12.msc and click “Ok”

out-gridview
This doesn’t count as a GUI!

Which will open up our “missing” configuration manager!

SQLServerConfigurationManagerEnd.PNG
I may have just been doing something stupid though…

Exit:

PowerShell! Helping make my life easier since…whenever I actually figure it out. 😐

Why I Powershell my Laptop off

Could be my shortest blog post so far…

Intro

Kalen Delaney ( blog | twitter ) has an excellent blog post about Windows Fast Startup and, while I’m not going to repeat what she has said here because, like I already mentioned, it’s an excellent post and I encourage you to read it ( and maybe give her a lil’ subscribe 😉 ), what I will mention is that I encountered this feature with my new laptop and had it interfering with my SQL Server testing (again read her post as to possible causes why).

Using Powershell for documenting Replication had me wondering if there was a way I could get around this using Powershell. So while this is another post that is not about SQL Server, it is about Powershell.

Hey, at least I’m consistent in my consistencies.

What’s the Problem?

A quick lmgtfu, brought me to the following page and command:

shutdown /s

Which pops open a window saying the computer will shutdown and, after a delay, that’s what it does.

At this stage I’ve read enough documentation to know that
shutdown /s
doesn’t follow the standard Verb-Noun convention of Powershell and that delay was slightly annoying.

Plus, everyone raves about the Get-Help commandlet so I figured I would try that.

Get-Help *shutdown*

Gave me a list of commands and one of them seemed to fit what I wanted.

Get-Help Stop-Computer;

Powershell_stopcomputer

Summary

3 things here.

  1. You now know how I turn my computer off all the time
  2. It’s amazing what you can do with Powershell, and
  3. Kalen says

    So you might already know, but I didn’t know, until I learned it, of course.

I didn’t know, but found a work-around so didn’t learn it.
I’d advise you to follow Kalen’s approach (as I’m going to try from now on) but, hey, at least you now know mine.