Specify partial directory to use in Powershell Invoke sqlcmd

Question

Need to be able to run .sql files individually that are located in multiple sub directories. This script will be ran on multiple computers where the directory wont be the same but the sub directories will be. So I want to be able to define the directory as a variable like this.

$Path = 'D:\Source\Database'
invoke-sqlcmd -Username $username -Password $password -inputfile "$Path*\SQLServer\create_types.sql" -serverinstance "localhost" -database "test" | Out-File -FilePath "c:\testoutput.txt"

Answer 1

Use Get-ChildItem with -recurse and pipe into a ForEach loop.

$Path = 'D:\Source\Database'
Get-ChildItem "$Path\*\SQLServer\create_types.sql" -recurse|ForEach{
    invoke-sqlcmd -Username $username -Password $password -inputfile $_.FullName -serverinstance "localhost" -database "test"
} | Out-File -FilePath "c:\testoutput.txt"