2

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?

3
  • Can provide an example script that you might want executed?
    – user507
    Jun 24, 2013 at 12:31
  • It is a bit confusing what exactly you're looking to do. What do you mean, "execute a script in all schemas"? You mean create an object or something in the schemas?? Jun 24, 2013 at 12:45
  • the simplest way to "PROMPT THE USER FOR EXCEPTED SCHEMAS" is to use a simple gui - OR use READ-HOST
    – Jimbo
    Aug 8, 2013 at 19:18

1 Answer 1

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
1
  • 1
    Just a side note, the OP is on SQL Server 2005 according to tags. Might want to note your solution would require SQLPS be installed.
    – user507
    Nov 10, 2014 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.