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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $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.
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…
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $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:
This example is one of the main reasons why we wrote this command!
Great real world example and great approach!
Nicely done mate!