Powershell SQL Script Execution in Selected Schemas Only

Question

I have many schemas in one database. In that database I have some SQL scripts to be executed. I want to execute those SQL scripts in only selected schemas.

Suppose I have 10 schemas in total (A to J). I want to execute a SQL script in all schemas except B,G,H and prompt the user for the excepted schemas. How can I do this?

Answer 1

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