Finding Parameters that do not match Column Names

You think this will take me hours? Ha! Think again.

Words: 437

Time to read: ~ 2 minutes

Script Link: https://github.com/shaneis/RandomScripts/blob/master/WhereParameterNameDoesNotMatchColumnName.ps1

Quick one here.

I was asked to standardise stored procedures we use for common support cases before we hand them over to IT Helpdesk.

One of the comments that came back from the Helpdesk while testing was that the parameter names that they had to put values in for didn’t match what they saw in the application.

Luckily for me (or unluckily) the application was a third party developed one and they didn’t bother renaming the columns. So if the column is called create_date in the database then the application is going to show create_date.

However, if I created a parameter called DateCreated or even CreateDate, then they don’t want it.

So the question is…

…how can I search for any parameter that does not match the column name?

And to do this against how many different parameters against how many different databases!

This has the potential to not be fun!

Well I recently used dbatools ( blog | twitter ) command Find-DbaStoredProcedure  to find a bug so is there anyway that I can use it again to find these differences?

We know from the documentation page that it can take a regex so can we use that to find parameters and the column names that they’re used against? Let’s find out.

First Attempt


$PatternMatch = '(?<ColumnName>\w+)\s*=\s*@(?<ParameterName>\w+)'
$FindStoredProcedureParameters = @{
SqlInstance = 'localhost\SQLServer2K16'
Database = 'master'
Pattern = $PatternMatch
IncludeSystemDatabases = $true
}
Find-DbaStoredProcedure @FindStoredProcedureParameters

I should explain a couple of things, starting with the regex, just in case.

  •  (?<ColumnName>\w+) is to find the column name, I’m just trying to find a word (\w+) and I’ve given it the match name of ColumnName.
  • \s*=\s*@ is saying any number of spaces, followed by an equal sign (=), any number of spaces again, and finally the at symbol (@).
  • (?<ParameterName>\w+) is to find the parameter name. I’m just trying to find a word (\w+) and I’ve given it the match name of ParameterName.

I’ve included the switch IncludeSystemDatabases = $true here because I’m looking at the master database and the cmdlet will skip it otherwise.

firstattempt

It works but it doesn’t give me exactly what I wanted. It finds the columns and the parameters but not where they don’t match.

That’s fine…

…this is why I specified names for the matches. Let’s just see if we can use them. I stick on a | Get-Member onto the end and see…

gm

Oh. I don’t see any way to do this without adding on some functionality. Luckily this is PowerShell we’re talking about!

I threw on a couple of ForEach-Object, a Select-String, and a Where-Object and we are good to go!

Finished Product


$PatternMatch = '(?<ColumnName>\w+)\s*=\s*@(?<ParameterName>\w+)'
$FindStoredProcedureParameters = @{
SqlInstance = 'localhost\SQLServer2K16'
Database = 'master'
Pattern = $PatternMatch
IncludeSystemDatabases = $true
}
Find-DbaStoredProcedure @FindStoredProcedureParameters |
ForEach-Object Process {
$ParentRow = $_
$_ | ForEach-Object StoredProcedureTextFound | Select-String Pattern $PatternMatch AllMatches | ForEach-Object Matches | ForEach-Object Process {
[PSCustomObject]@{
ServerName = $ParentRow.SqlInstance
DatabaseName = $ParentRow.Database
SchemaName = $ParentRow.Schema
ProcedureName = $ParentRow.Name
TextFound = ($_.Groups | Where-Object Name -eq '0').Value
ColumnName = ($_.Groups | Where-Object Name -eq 'ColumnName').Value
ParameterName = ($_.Groups | Where-Object Name -eq 'ParameterName').Value
}
}
} | Where-Object { ($_.ColumnName -ne $_.ParameterName) } |
Select-Object Property * Unique

And we get the following results:

Finished
I’m pretty happy with this one 😀

 

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

2 thoughts on “Finding Parameters that do not match Column Names”

Leave a Reply

%d bloggers like this: