Hopefully I can help you answer this question if I understand the question correctly. You wish to run the same query against multiple schemas in a database. I assume each schema has identical tables. As suggested below by Shawn you will need to import the module SQLPS in Powershell to run the scripts below.
Assume the database with 3 schemas as follows
CREATE DATABASE [aSchemaCheck]
GO
USE [aSchemacheck]
GO
CREATE SCHEMA [Aschema] AUTHORIZATION [db_owner]
GO
CREATE TABLE ASchema.Name
(
time time NOT NULL,
Name char(10) NULL,
)
GO
CREATE SCHEMA [BSchema] AUTHORIZATION [db_owner]
GO
CREATE TABLE BSchema.Name
(
time time NOT NULL,
Name char(10) NULL,
)
GO
CREATE SCHEMA [CSchema] AUTHORIZATION [db_owner]
GO
CREATE TABLE CSchema.Name
(
time time NOT NULL,
Name char(10) NULL,
)
GO
What you can do is use Invoke-SQLCmd and create a $query string and replace the Schema in the query as follows
$query = "INSERT INTO [$Schema].[Name]
([time]
,[Name])
VALUES
(CONVERT(time,GetDate())
,'AName')
GO"
Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $query
You can specify the schemas in the script and loop through them as follows by putting the above query inside a loop as follows
$Schemas = 'ASchema','BSchema','CSchema'
foreach($Schema in $Schemas)
{
}
If we you want to run the query against all user defined schemas you can do the following
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
$db = $srv.Databases[$Database]
$Schemas = $db.Schemas|Where-Object{$_.IsSystemObject -eq $false}|select Name -ExpandProperty Name
foreach($Schema in $Schemas)
{
}
If you want to select the Schemas you can do so using Out-GridView -Passthru press CTRL and click to choose multiple
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
$db = $srv.Databases[$Database]
$Schemas = $db.Schemas|Where-Object{$_.IsSystemObject -eq $false}|Out-GridView -PassThru|select Name -ExpandProperty Name
foreach($Schema in $Schemas)
{
$query = "INSERT INTO [$Schema].[Name]
([time]
,[Name])
VALUES
(CONVERT(time,GetDate())
,'AName')
GO"
Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $query
}
if you wanted to use a .sql file against each schema you would use
Invoke-Sqlcmd -ServerInstance $Server -Database $Database -InputFile $SQLFile