Watch out for square brackets when using Select-String to search for SQL objects

Powershell’s Select-String cmdlet is powerful, well, sometimes too powershell you’d want to dumb it down a notch. That’s what happened to me today.

Here is the scenario. I have the name of a column in the form of [schema_name].[table_name].[column_name] and I want to see if [schema_name].[table_name] part of the name is found amongst a list of tables. I have set up two test scenarios below for demonstration purpose:

Table List
[sys].[objects]
[sys].[columns]
[sys].[types]

Column
1. For [sys].[objects].[type_desc], Select-String should return something as the table name [sys].[objects] forms part of the column name.
2. For [sys].[tables].[type_desc], Select-String should return nothing as no tables from the list matches [sys].[tables].

Expressed in powershell:

$table_array = "[sys].[columns]", "[sys].[types]", "[sys].[objects]"
$sys_objects_name = "[sys].[objects].[type_desc]"
$sys_tables_name = "[sys].[tables].[type_desc]"

"`nLooking for a table that is part of $sys_objects_name..."
Select-String -InputObject $sys_objects_name -Pattern $table_array

"`nLooking for a table that is part of $sys_tables_name..."
Select-String -InputObject $sys_tables_name -Pattern $table_array

In this instance, -Pattern takes an array and for each one of the array items it is matched against -InputObject. In other words, we are trying to find a -Pattern that is a subset of -InputObject.

Running the above script yields the output below:

Looking for a table that is part of [sys].[objects].[type_desc]...
[sys].[objects].[type_desc]

Looking for a table that is part of [sys].[tables].[type_desc]...
[sys].[tables].[type_desc]

Notice how both Select-String returned something? That’s wrong as we don’t expect the second Select-String to return anything. After trying to debug with a few different scenarios, it dawned on me that Select-String uses regular expression matching by default. It was one of those “OF COURSE!” moments in hindsight. Most of the time it is ok if you are just trying to do some simple string matching but when working with SQL (square bracket) quoted names, it can wreak havoc.

The solution is to turn regular expression matching off and to do that you just need to specify the flag -SimpleMatch:

$table_array = "[sys].[columns]", "[sys].[types]", "[sys].[objects]"
$sys_objects_name = "[sys].[objects].[type_desc]"
$sys_tables_name = "[sys].[tables].[type_desc]"

"`nLooking for a table that is part of $sys_objects_name..."
Select-String -InputObject $sys_objects_name -Pattern $table_array -SimpleMatch

"`nLooking for a table that is part of $sys_tables_name..."
Select-String -InputObject $sys_tables_name -Pattern $table_array -SimpleMatch

Running the script again now yields the correct output:

Looking for a table that is part of [sys].[objects].[type_desc]...
[sys].[objects].[type_desc]

Looking for a table that is part of [sys].[tables].[type_desc]...

–oo00oo–
For more info, please refer to the links below:
Select-String