CODEWITHSUNDEEP
sql-serverpowershell
lonelydev101
lonelydev101

Reputation: 1901

How to implement Where-Object in SQL-Invoke command

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.

Upvotes: 0

Views: 310

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200353

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.

Upvotes: 2

Related Questions