Desk Column Variations Half 03 – Evaluate-SqlTableColumns – No Column Title

Phrases: 470

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.

Hello,

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

AND

column_a, column_a –> completely different order within the desk

Thanks upfront

Michal

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

1. Column orders, e.g. col9 has id 6 in dbo.DifferenceTable01 however id 5 in dbo.DifferenceTable02.
2. Column case sensitivity, e.g. col7 doesn’t match COL7.
3. Column presence, e.g. col3 doesn’t exist in dbo.DifferenceTable01 in any respect.

Whereas 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

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.