Time to learn: ~ 2 minutes
Don’t speak to me about it!
4 years in the past (I do know, the place did the time go?), I wrote about Desk Column Variations with T-SQL and PowerShell.
A Michal commented on the publish, asking easy methods to get a particular output from his search.
Thanks on your sharing. What if I additionally need to evaluate case sensitively columns and the order of them (syncwindows). How can I offered it on powershell.
I imply that within the remaining desk I need to present additionally one thing like: column_a, column_A –> case delicate
column_a, column_a –> completely different order within the desk
I confess that I by no means bought round to answering Michal till a number of weeks in the past when I discovered myself with some uncommon free time.
Since then, I’ve written a script, slapped it right into a perform, and threw it up on Github.
Right here’s hoping that it does what you need this time Michal, thanks for ready.
Shall I Evaluate Thee to One other Desk?
The very first thing that we have to do is have a few SQL tables to match.
So, I threw up a Docker container and created a few tables with practically the identical structure.
(Get-DbaDbTable -SqlInstance localhost -Desk 'dbo.DifferenceTable01', 'dbo.DifferenceTable02').Columns | Choose-Object -Property Father or mother, Title, ID, DataType | Format-Desk -GroupBy Father or mother
You’ll be able to see that there are round three variations right here
- Column orders, e.g.
col9has id 6 in
dbo.DifferenceTable01however id 5 in
- Column case sensitivity, e.g.
- Column presence, e.g.
col3doesn’t exist in
dbo.DifferenceTable01in any respect.
Evaluate-Object has the
-CaseSensitive swap, I don’t suppose that it will be useful in all these circumstances. Or else I didn’t need to use that command this time round.
So, I wrote a perform to get the output we wished, and sure, I now embody myself amongst that listing of individuals wishing for that output.
I’m allowed to be biased in direction of the issues that I write 🙂
Evaluate-SqlTableColumns -SqlInstance localhost -Table1 'dbo.DifferenceTable01' -Table2 'dbo.DifferenceTable02' | Format-Desk
I’ve tried to incorporate every thing you would need within the perform output, i.e. column names, column ids, and statuses.
One thing I’ve began to do recently is wrapping a
[Diagnostics.StopWatch] in my verbose assertion to see the place potential sluggish components of the perform are.
I’d wish to suppose that 0.2 seconds for this instance aren’t too dangerous.
$x = Evaluate-SqlTableColumns -SqlInstance localhost -Table1 'dbo.DifferenceTable01' -Table2 'dbo.DifferenceTable02' -Verbose $x | Format-Desk
Thou hast much less columns than thine brother…
Be happy to make use of and abuse this perform to your hearts content material. I do know that there are some things that I’d add to it. Evaluating throughout completely different cases being an apparent one which I’d wish to put in.
Hopefully although, somebody on the market will discover it useful.
Right here’s taking a look at you, Michal.