Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Any one please help me on this one also..I have got many schemas under one database. And in that database I have some SQL Scripts to be executed. My issue is that I should be able to execute those sql scripts in only selected schemas.

That is::Suppose I have got totally 10 schemas(A to J). And I want to execute a script in all schemas except B,G,H (PROMPT THE USER FOR EXCEPTED SCHEMAS).How can I do this??

Thanks in advance..

share|improve this question
    
Can provide an example script that you might want executed? –  Shawn Melton Jun 24 '13 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?? –  Thomas Stringer Jun 24 '13 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 '13 at 19:18

1 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
share|improve this answer
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. –  Shawn Melton Nov 10 at 20:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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