Words: 819
Time to read: ~ 4 minutes
I recently ran across a problem with a PowerShell script that I was writing which was giving me a weird error.
Now I say “a weird error” because my script was doing what I was telling it to do and wasn’t doing what I thought that it would do instead!
Anyway, when I figured out how to fix my problem, i.e. not be so stupid and actually read the error message, I thought that I would share what I had learned.
So here it is…
Background
Now this may come as a shock to you but there exists out there some unfortunate souls that for one reason or another have the following tables, or their like, in their databases.
Yes, I’m talking about the numbered or dated table. Now this may exist for a number of reasons e.g. a poor man’s backup, a 3rd Party vendor with a passion for chaos, or an unfortunate case of too much caffeine in the fingers when trying to create a table.
The Script
I getting a quick row count of the tables in a database which is easily do-able in PowerShell
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
| $DatabaseParam = @{ | |
| SqlInstance = 'localhost\SQLDEV2K14' | |
| Database = 'DMODisabled' | |
| } | |
| Get-DbaDatabase @DatabaseParam | | |
| Select-Object –ExpandProperty Tables | | |
| ForEach-Object –Process { | |
| [PSCustomObject]@{ | |
| Name = $_.Name | |
| RowCount = $_.RowCount | |
| } | |
| } |
Note: You don’t even have to do this, I just like the output. You can change everything after the -ExpandProperty Tables | to Select-Object -Property Name, RowCount. Meaning you won’t have to worry about ForEach-Object or [PSCustomObject] if you don’t want to!
Which gives us the lovely output of…
The Problem
Hmmm, best I omit those stupid tables but as you can see from the screen shot there’s no rhyme or reason to their naming. It’s not like most you’ll see that are “tablename_date” so how to go about this?
Well, at this stage, I knew enough about PowerShell to know about the Where-Object command, and I knew enough regex to get a basic one going.
Let me show you what my brain was thinking when writing this.
Hmmm so we’ll start with a Where-Object on the name…
Where-Object { $_.Name
Supposed it’s best we use a -notlike or a -notmatch…
Where-Object { $_.Name -notlike
Where-Object { $_.Name -notmatch
And best I be specific in case I exclude stuff I don’t want… so lets’s say “everything, then anywhere up to 6 digits, then the end of the line”
Where-Object { $_.Name -notlike "*\d{,6}$" }
Where-Object {$_.Name -notmatch "*\d{,6}$" }
Altogether giving me these scripts
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
| $DatabaseParam = @{ | |
| SqlInstance = 'localhost\SQLDEV2K14' | |
| Database = 'DMODisabled' | |
| } | |
| Get-DbaDatabase @DatabaseParam | | |
| Select-Object –ExpandProperty Tables | | |
| Where-Object { $_.Name -notlike "*\d{4,6}$" } | | |
| ForEach-Object –Process { | |
| [PSCustomObject]@{ | |
| Name = $_.Name | |
| RowCount = $_.RowCount | |
| } | |
| } | |
| Get-DbaDatabase @DatabaseParam | | |
| Select-Object –ExpandProperty Tables | | |
| Where-Object { $_.Name -notmatch "*\d{4,6}$" } | | |
| ForEach-Object –Process { | |
| [PSCustomObject]@{ | |
| Name = $_.Name | |
| RowCount = $_.RowCount | |
| } | |
| } |
The problem that I ran into is that -notlike just ignored my instructions…
While, when I used -notmatch, I got the error message.
Where-Object : parsing “*\d{,6}$” – Quantifier {x,y} following nothing.
My Foolishness
Glancing at the error message, the first things that stick out are the bits “{x,y}” so I change my regex to be anywhere from 1 to digits "*\d{1,6}$"
Why are you glancing, read the error message!
That doesn’t work, so I again quickly scan the error message and see the bit “following nothing”
“Quickly scan”?! No, actually read the error message!!
Maybe it’s something to do with my $ to say it has to be the final bit of the line. That would technically be “following nothing”. So I change my where clause to remove the $.
Again that doesn’t work so I admit defeat.
DON’T ADMIT DEFEAT, JUST READ THE ACTUAL ERROR!!
Popping the error message into Google, I hit upon a StackOverflow answer that spells it out for me.
The Answer
Summed up beautifully (and yes, I said “b-e-a-utifully” when spelling that) is this sentence:
The character
*is special in regular expressions as it allows the preceding token to appear zero or more times. But there actually has to be something preceding it.
And I finally understand.
I go back and actually read the error message, slowly and actually devoting some attention to it.
Yes! About damn time!
“Quantifier {x,y} following nothing”…there’s a quantifier following nothing. It says it right there! A straight translations from T-SQL to PowerShell doesn’t work here; the PowerShell regex character * expects something before it and I’ve given it nothing!
Finally understanding, I go back and remove the offending * from my code .
At this stage, I’ve focused solely on the -notmatch code and also change it from 4 to 6 digits!
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
| $DatabaseParam = @{ | |
| SqlInstance = 'localhost\SQLDEV2K14' | |
| Database = 'DMODisabled' | |
| } | |
| Get-DbaDatabase @DatabaseParam | | |
| Select-Object –ExpandProperty Tables | | |
| Where-Object { $_.Name -notmatch "\d{4,6}$" } | | |
| ForEach-Object –Process { | |
| [PSCustomObject]@{ | |
| Name = $_.Name | |
| RowCount = $_.RowCount | |
| } | |
| } |
And we finally have sweet, succulent success.
Final Words
If there is an error on the screen, actually take some time and read what it says. It could save you so much time in the long run.
Oh and also…
The character
*is special in regular expressions as it allows the preceding token to appear zero or more times. But there actually has to be something preceding it.
Thank you Joey, whoever you are.
I put this as #NewSqlBlogger to incourage new bloggers, who encounter a problem like this, to blog about it. Every piece of knowledge shared matters. Also it’s to do with SQL tables so it still falls under the category “New SQL Blogger”.
Thanks. Trees, forest, its nice to have it spelt out sometimes. I just had to add a ^
Where-Object { $_.Name -notmatch ‘^*batdata*’ }
This was brilliantly elucidated! I not only learned the fix but enjoyed the process. If you tire of SD pick you pen and find a cabin!