How to implement Where-Object in SQL-Invoke command

Question

I am trying to filter some tables from my database which exists in my solution folder. I want to filter all tables that I am pulling from SQL Server:

$existingTables = "Table1", "Table2", "Table3", "Table4"

#getting all tables except existing ones
#SqlQuery = "SELECT name FROM sys.Tables order by name asc"
$filteredTables = ((Invoke-SQL -DataSource $ServerName -DatabaseName $DatabaseName -UserID $UserID -Password $Password -SqlCommand $SQLQuery).name | ? {$_ -notcontains $existingTables})
#$filteredTables returns all tables, including the existing ones...

I've tried $_.name and it is the same result.

Why not add a where name not in() into the select statement? — vonPryz
– vonPryz, Commented Dec 5, 2018 at 11:59
$_ -notcontains $addedTables -> $addedTables -notcontains $_. You switched the operands. For -[not]contains the correct order is <reference_array> -notcontains <item>. Otherwise use -[not]in (<item> -notin <reference_array>). The latter is not available prior to PowerShell v3, though. — Ansgar Wiechers
– Ansgar Wiechers, Commented Dec 5, 2018 at 12:00

Ansgar Wiechers · Accepted Answer · 2018-12-05 13:24:55Z

2

You're using the operands of the -notcontains operator in the wrong order. The correct syntax is

reference_array -notcontains item

In your case:

$existingTables -notcontains $_

or

$existingTables -notcontains $_.Name

if you don't expand the property Name.

If you want to use the reference array as the second operand you must use the -notin operator:

$_ -notin $existingTables

However, that operator is not available prior to PowerShell v3.

Alternatively, you could add an exclude clause to your SQL statement, as @vonPryz suggested in the comments. Take care to not open yourself to SQL injection when doing that, though.

Don't do this:

$SQLQuery = @"
SELECT ...
FROM ...
WHERE name NOT IN ('$($existingTables[0])', '$($existingTables[1])', ...)
"@

Use a prepared statement (or "parameterized query" as Microsoft calls them). I don't recognize the cmdlet you're using, though (doesn't seem to be Invoke-Sqlcmd), so I can't tell you how to do it with that cmdlet.

edited Dec 5, 2018 at 13:24

answered Dec 5, 2018 at 13:12

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lonelydev101 Over a year ago

It crossed on my mind that I replaced the order but I didn't try :) Thank you very much!

Collectives™ on Stack Overflow

How to implement Where-Object in SQL-Invoke command

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related