My script in powershell runs: test.ps1 -dbo test1 => this works, it enters provided database
SELECT name FROM sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb', 'test1');
but if i want to enter multiple databases e.g test.ps1 -dbo test1, test2, test3 => this is read as one string
SELECT name FROM sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb', 'test1, test2, test3');
How can I make in Powershell to split string into seperate, like that:
SELECT name FROM sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb', 'test1', 'test2', 'test3');
code:
Param(
[Parameter(Mandatory=$false, ValueFromPipeline=$true)]
[Alias("dbo")]
[String[]]
$databaseName
)
$dbquery = @"
SELECT name FROM sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb', '$databaseName');
"@
Invoke-Sqlcmd -ServerInstance "$databaseIP" -Database "master" -Username $databaseUser -Password $databasePass -Query $dbquery
I tried with split and join but it does not work as i expected
NOTE: Any time you are taking parameters and directly putting them into a SQL string, you can open yourself up to malicious coders and SQL injection issues. It is recommended to sanitize your inputs (make sure the input is valid) before you use them in a SQL string. Exploits of a Mom
You are getting exactly what you're asking for by putting an array of strings into a string without pre-processing. A simple way of doing this is to fill in the stuff between the array members first, so add the ', '
with a -join
like this (Adding plural array name for an array as suggested by @CaiusJard):
Param(
[Parameter(Mandatory=$false, ValueFromPipeline=$true)]
[Alias("dbo")]
[String[]]
$databaseNames
)
$databaseNameString = $databaseNames -join "', '"
$dbquery = @"
SELECT name FROM master.dbo.sysdatabases
where name not in ('master', 'tempdb', 'model', 'msdb', '$databaseNameString');
"@
Invoke-Sqlcmd -ServerInstance "$databaseIP" -Database "master" -Username $databaseUser -Password $databasePass -Query $dbquery
I hope this helps you out.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With