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?

• user507: Can provide an example script that you might want executed?
• Thomas Stringer: 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??
• Jimbo: the simplest way to "PROMPT THE USER FOR EXCEPTED SCHEMAS" is to use a simple gui - OR use READ-HOST

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

• user507: 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.