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

You can see that there are around three differences here
- Column orders, e.g.
col9
has id 6 indbo.DifferenceTable01
but id 5 indbo.DifferenceTable02
. - Column case sensitivity, e.g.
col7
does not matchCOL7
. - Column presence, e.g.
col3
doesn’t exist indbo.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.