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.
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
column_a, column_a –> different order in the table
Thanks in advanceMichal
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
You can see that there are around three differences here
- Column orders, e.g.
col9has id 6 in
dbo.DifferenceTable01but id 5 in
- Column case sensitivity, e.g.
col7does not match
- Column presence, e.g.
col3doesn’t exist in
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 -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.