Powershell and SQL Server – 5- Execute Parameterized SQL Script on Multiple SQL Server Instance with Invoke-Sqlcmd

I wrote the below script to run a sql script on multiple sql instances and on all the database with Exclude Database Filter.

param ([string]$DatabaseToBeExcluded)   
if (-not (Get-Module SQLPS)) {Import-Module SQLPS} 

$ExcludeDatabases=$DatabaseToBeExcluded #'1,2,3,4' 
$localpath=split-path $SCRIPT:MyInvocation.MyCommand.Path -parent 
  foreach($line in (Get-Content $localpath'ServerList.txt')) 
  { 
    $line 
    Invoke-Sqlcmd -InputFile $localpath'PermissionScript.sql'-Variable ExDb=$ExcludeDatabases -ServerInstance $line -ErrorAction SilentlyContinue -Verbose -QueryTimeout 0 -Database master 
  }

SQL Script

--:setvar ExDb "1,2,3,4"

SET NOCOUNT ON
DECLARE @ExDb varchar(100)
DECLARE @Start int
DECLARE @End int
DECLARE @Databasename varchar(100)
DECLARE @sql nvarchar(500)
SET @ExDb= '$(ExDb)' 

DECLARE @DBName TABLE (ID INT IDENTITY(1,1),DBName varchar(100))
SET @sql='SELECT name from sys.databases where convert(varchar(10),database_id) not in ('+@ExDb+') and state=0'
INSERT INTO @DBName 
EXEC (@sql)
SET @End=@@Rowcount 

  SET @Start=1
    WHILE @Start <=@End
      BEGIN
        SELECT @Databasename=DBName FROM @DBName WHERE ID=@Start
        SET @sql=''
          BEGIN
            SET @sql='your commands or queries'
          END
        exec (@sql)
      SET @Start=@Start + 1
      END

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *