Table Column Differences Part 03 – Compare-SqlTableColumns

Words: 470

Time to read: ~ 2 minutes

Don’t talk to me about it!

Four years ago (I know, where did the time go?), I wrote about Table Column Differences with T-SQL and PowerShell.

A Michal commented on the post, asking how to get a specific output from his search.

Hi,

Thanks for your sharing. What if I also want to compare case sensitively columns and the order of them (syncwindows). How can I presented it on powershell.

I mean that in the final table I want to show also something like: column_a, column_A –> case sensitive

AND

column_a, column_a –> different order in the table

Thanks in advance

Michal

I confess that I never got around to answering Michal until a few weeks ago when I found myself with some rare free time.

Since then, I’ve written a script, slapped it into a function, and threw it up on Github.

Here’s hoping that it does what you want this time Michal, thanks for waiting.

Shall I Compare Thee to Another Table?

The first thing that we need to do is have a couple of SQL tables to compare.

So, I threw up a Docker container and created a couple of tables with nearly the same layout.

(Get-DbaDbTable -SqlInstance localhost -Table 'dbo.DifferenceTable01', 'dbo.DifferenceTable02').Columns |
        Select-Object -Property Parent, Name, ID, DataType |
        Format-Table -GroupBy Parent
I’m liking the new PowerShell formatting

You can see that there are around three differences here

  1. Column orders, e.g. col9 has id 6 in dbo.DifferenceTable01 but id 5 in dbo.DifferenceTable02.
  2. Column case sensitivity, e.g. col7 does not match COL7.
  3. Column presence, e.g. col3 doesn’t exist in dbo.DifferenceTable01 at all.

While Compare-Object has the -CaseSensitive switch, I don’t think that it would be helpful in all these cases. Or else I didn’t want to use that command this time around.

So, I wrote a function to get the output we wanted, and yes, I now include myself among that list of people wishing for that output.

I’m allowed to be biased towards the things that I write 🙂

Compare-SqlTableColumns

Compare-SqlTableColumns -SqlInstance localhost -Table1 'dbo.DifferenceTable01' -Table2 'dbo.DifferenceTable02' |
        Format-Table

I’ve tried to include everything you could want in the function output, i.e. column names, column ids, and statuses.

Something I’ve started to do lately is wrapping a [Diagnostics.StopWatch] in my verbose statement to see where potential slow parts of the function are.

I’d like to think that 0.2 seconds for this example aren’t too bad.

$x = Compare-SqlTableColumns -SqlInstance localhost -Table1 'dbo.DifferenceTable01' -Table2 'dbo.DifferenceTable02' -Verbose

$x | Format-Table

Thou hast less columns than thine brother…

Feel free to use and abuse this function to your hearts content. I know that there are a few things that I’d add to it. Comparing across different instances being an obvious one that I’d like to put in.

Hopefully though, someone out there will find it helpful.

Here’s looking at you, Michal.

TSQL Tuesday 106 – Trigger Headaches or Happiness: Capturing Database Creations.

I see your hidden database table trigger and raise you a more hidden server one!

Words: 306

Time to read: ~ 1.5 minutes

Continue reading “TSQL Tuesday 106 – Trigger Headaches or Happiness: Capturing Database Creations.”

Automating Conference Speaker Notifications

In case I, as a volunteer, fall asleep from lack of coffee 🙂

Words: 367

Time to read: ~ 2 minutes

Link to script: https://github.com/shaneis/RandomScripts/commit/9d6967fa56a75342f00e5ddbcd066adc387f2618

Continue reading “Automating Conference Speaker Notifications”

Importing Excel into SQL Server using PowerShell

Ah T-SQL Tuesday, is it that time again? And the 94th one at that! Wow!

Words: 797
Time to read: ~4 minutes

Update: 2017-09-20 Thanks to Rick Fraser for pointing out I showed a $ServerConnection but hadn’t defined it in the function or separately or at all! Thanks Rick!

Continue reading “Importing Excel into SQL Server using PowerShell”

Exporting Special Characters out of SQL Server using PowerShell.

PowerShell is ußer-useful!

So I’ve talked before about keeping new lines when copying results to a different window in SQL Server and about copying new lines out of SQL Server into reports.

These topics have come about as they are both issues that I’ve had to deal with. Well, another of those issues is dealing with exporting special characters out of SQL Server using PowerShell.

The Lay-out.

We already have our table called “dbo.NewLineNotes” from before when we were trying to copy new lines out of SQL Server so we’re going to add another row.
Now personal experience for me centered around the German Eszett (“ß”) but you may encounter this with other characters.

-- Insert some special characters...
INSERT INTO dbo.NewLineNotes (Notes)
VALUES (N'This is a ß')

Now if you were to use the code from keeping new lines post…


$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes'
$InvSQLParams = @{
ServerInstance = '0738-5CG140G7T\SQLSERVER2K16'
Database = 'Pantheon'
Query = $NewLineQuery
}
Invoke-Sqlcmd @InvSQLParams |
Select-Object Property Notes |
Export-Csv Path 'C:\Users\soneill\Desktop\In Progress\Test\NewLines.csv' NoTypeInformation

and open up the csv file we would get…

EncodingIssue
My german is non-existant but I know that’s wrong!

“What do we do when we fall down?”…

Well with SQL Server, I normally break things down into the smallest parts and slowly build it up until it breaks. For this, it breaks when we get to Export-CSV as everything before it works!

Troubleshooting
What we want…

PowerShell is even easier for troubleshooting methodology as , and we’ve talked about it before, Get-Member and Get-Help are there to help us!

We know that it’s Export-CSV that is somehow screwing up our special character so the obvious next step…

help Export-CSV -Full;

And we can see a parameter just shine at us!

Encoding
Looks like ASCII is not for me!

So we have to define an “Encoding” do we? I used “UTF8” and modified my query…


$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes'
$InvSQLParams = @{
ServerInstance = '0738-5CG140G7T\SQLSERVER2K16'
Database = 'Pantheon'
Query = $NewLineQuery
}
Invoke-Sqlcmd @InvSQLParams |
Select-Object Property Notes |
Export-Csv Path 'C:\Users\soneill\Desktop\In Progress\Test\NewLines.csv' NoTypeInformation Encoding UTF8;

EncodingIssueResolved
Eszett? More like EZ-zett!

And special characters are no longer an issue for us 🙂

 

Copying New Line Data out of SQL Server

 

A lot of the time, DBAs are asked to run adhoc reports for various business people and, more often than not, the expected medium for these reports is Excel.

Now for the most part this seems simple enough…

  • Run the T-SQL report
  • Highlight the results
  • Copy the results
  • Paste into an Excel worksheet

Simples!…right?

How do you deal with carriage returns though? New line feeds? Tabs? Commas when you’re trying to comma delimit?

Try and copy them into an Excel worksheet and what you’re going to get is confusion, alarm, and vexation.

Not exactly the clear reporting that the business people are hoping for.

So what can we do? Panic? Grab another coffee? Roll your “r’s”?

Yes, yes, and not yet…


Karaoke…

I have mentioned before that we can use CHAR(10) and CHAR(13) for new lines and carriage returns in SQL Server so I’ll leave it up to an exercise to the reader to create a table with these “troublesome” bits of information in them (plus if you came here from Google, I assume you already have a table with them in it).

For me, I’ve just created a single table dbo.NewLineNotes that has a single entry with a new line in it.

CopyingNewLineTwoLines
SQL Server is left, Report is right

So a straight-up copy and paste isn’t going to cut it here. If we have more than 1 row, we’re not going to get a 1 entry to 1 row in the report that we are looking for. How do people deal with this?

1 Way:

Well, depending on what tool you have, the answer could be as simple as a right-click and selecting “Open in Excel”

RedGate_OpenInExcel
Intact but on 1 line 🙂

Or Another:

Let’s proceed with the impression that you do not have RedGate tools (cough free trial cough) and cannot avail of the right-click righteousness, what do you do then.

Well…have you thought about PowerShell?

Hear me out on this but you probably already have your query but found the new lines are screwing up the report. So let’s throw that query into a variable

$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes'

Then what we have to do is somehow connect to the SQL Server instance and database.

Let’s go with the very basics here as that’s all we really need. Invoke-SqlCmd, and yes I know it has problems. I’ve linked and talked about them before. It works for us in this situation though.


$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes'
$InvSQLParams = @{
ServerInstance = 'localhost\SQLSERVER2K16'
Database = 'Pantheon'
Query = $NewLineQuery
}
Invoke-Sqlcmd @InvSQLParams |
Select-Object ExpandProperty Notes

FirstResults
Yup, that’s good old VS code!

Now the more code-centered readers among you may have spotted and asked why I used -ExpandProperty and not just -Property , or even why I included it at all.
Well, apart from the thought that code online should be like code in scripts (legible with no aliases), we’re dealing with new lines here!
If we don’t specify ​-expand then what we actually get is…

SecondResults
comma delimited or ellipses delimited?

How does that help us with Reports?

If you work with PowerShell for the smallest amount of time, then I hope you’ve run into the command Export-CSV. See help for details…

help Export-Csv -Full

This will output a delimited file (defaults to comma but we can change that if we want) to wherever we want. We can then open it up in Excel or whatever other tool you use.

Let’s see if that splits our information into a new line!


$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes'
$InvSQLParams = @{
ServerInstance = '0738-5CG140G7T\SQLSERVER2K16'
Database = 'Pantheon'
Query = $NewLineQuery
}
Invoke-Sqlcmd @InvSQLParams |
Select-Object Property Notes |
Export-Csv Path 'C:\Users\soneill\Desktop\In Progress\Test\NewLines.csv' NoTypeInformation

ThirdResult
IT’S ALIVE!!! ugh I mean…IT WORKS!!!

Another another…

There are tons of different ways to do this but this is what I used.

Quick, dirty, and effective.

In the short term, I’m okay with that!

 

SQL Prompt: For Your Group By Problems

I’m going to point people to this that have “My Group By isn’t working” questions…

The Joys of SQL:

Did you know that the SQL language allows you to do amazing analysis of data such as aggregate functions?

SELECT t.session_id,
       t.request_id,
       SUM(t.user_objects_alloc_page_count) AS UserObjectAllocated,
       SUM(t.user_objects_dealloc_page_count) AS UserObjectDeallocated
FROM sys.dm_db_task_space_usage AS t
GROUP BY t.session_id,
         t.request_id;

Works Written
0’s! Amazing!

The Pains of SQL:

But…if you forget to put in the GROUP BY clause, as a ski instructor once said, you’re going to have a bad time!

Need Group By
Pizza…French Fries…Pizza

The Repetitiveness of Questioners:

So some eager yet lost scholar ventures into this land of aggregate functions, reads the error message and adds in a GROUP BY clause.

SELECT t.session_id,
       t.request_id,
       SUM(t.user_objects_alloc_page_count) AS UserObjectAllocated,
       SUM(t.user_objects_dealloc_page_count) AS UserObjectDeallocated
FROM sys.dm_db_task_space_usage AS t
GROUP BY t.session_id;

Needs second column
French Fries….

Now don’t scoff, this happens. I mean the error message is still red, looks nearly identical to the original one encountered, and can cause a rage-inducing damnation of SQL Server error messages.

The Enlightenment of Questioners:

Trawling the bulletin boards, question sites, and forums – okay maybe a quick question online, it’s called poetic exaggeration people! – they eventually learn the folly of their ways and correct their mistake.

SELECT t.session_id,
       t.request_id,
       SUM(t.user_objects_alloc_page_count) AS UserObjectAllocated,
       SUM(t.user_objects_dealloc_page_count) AS UserObjectDeallocated
FROM sys.dm_db_task_space_usage AS t
GROUP BY t.session_id,
         t.request_id;

Works Written
PIZZA!

The Euphoria of SQL Prompt:

Now I consider myself lucky that work has invested in the RedGate tools, and right now, especially SQL Prompt.

I’m not going to talk about “Save and Recover Lost Tabs” – saved my ass many times.
I’m not going to talk about “Code Formatting” – saved my sanity many times.
I’m going to talk about “Autocomplete”.

A well-known secret with SQL Prompt’s autocomplete is the snippets feature. With this, you can increase your productivity by 75% from typing out G R O U P [space] B Y and instead use gb and hit tab.

gb shortcut
Wait? I can order Pizza?

The Ecstasy of SQL Prompt:

Do not get me wrong, a 75% increase in productivity? I’ll take that!

That is a well-known secret though, and it’s slightly hard to get excited about a well-known secret.

However, what if I told you that SQL Prompt had another lesser-known secret that can increase your productivity and ensure that you do not forgot to add the necessary columns to your GROUP BY clause?

Interested? Ah c’mon!
You sure you’re not interested?…. That’s better!

So first of all, let us increase the number of non-aggregated columns in our SELECT to include database_id, is_remote_work, and exec_context_id. Including our session_id and request_id these are all columns that we are going to need to add to our GROUP BY clause, because…well…business logic.

Only problem is ain’t nobody got time for that.
SQL Prompt knows this and adds the following little snippet after a GROUP BY autocomplete.

Shortcut shortcut
Whoa! Whoa! You can deliver Pizza to me?

Hitting tab on that includes everything in the SELECT that is not part of an aggregate function, leaving us to concern ourselves with loftier things…

Final Works
Like whatever happened to Pizza in 30 mins or free?

Pizza:

Now I don’t work for pizza RedGate, I’m not affiliated with them, and I don’t get any money off of them. In fact, I’d say that they’d happily pay me not to write about them but when I found this autocomplete feature, I got too happy not to share it!

So save yourself the trouble of typing everything out and spare yourself the pain of error messages.

Use this lesser-known secret and have more time for pizza.