sql-serverpowershellsmo

Getting program module scripts using PowerShell


I am faced with the task of copying changes to program modules (stored procedures, functions and views) from one server to others, along with access rights. I decided that the easiest way to do this is to collect information in sys.objects about what was changed and then use PowerShell scripts to get the text of programs and execute them on another server. Please help me figure out how to get scripts of functions, views and access rights. For procedures it was possible to sort it out, but for functions, views and access rights it does not work out in any way.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null 
$srv = new-object Microsoft.SqlServer.Management.Smo.Server("srvMDM") 
$db = $srv.Databases.Item("MDM") 
$proc = $db.StoredProcedures | ?{ $_.Name -eq "parsing_remains_v1"} 
$retval = $proc.ScriptHeader($true) + $proc.TextBody 

Solution

  • I managed to solve the problem, but it's far from perfect. The following problems I could not solve:

    1 - since I didn't understand how I can create ALTER scripts and not CREATE - I abandoned Smo.Scripter (ScriptForAlter = $true gave an error)

    2 - since I did not understand how it is possible to run a PowerShell command on more than one line (without creating a separate .ps1 file), then my entire PowerShell script is transferred to one line Below is a PowerShell script and then a procedure in SQL Server that runs the script - my final solution

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null 
        $srv = new-object Microsoft.SqlServer.Management.Smo.Server("srvMDM") 
        $db = $srv.Databases.Item("MDM") 
        $proc = $db.UserDefinedFunctions | where { $_.Schema -eq "marat" -and $_.Name -eq "testfunction"} 
        $retval = $proc.ScriptHeader($true) + $proc.TextBody 
        ECHO $retval
        ECHO GO
        
        $objPermission=$proc.EnumObjectPermissions() | Select-Object objectschema, objectname, permissiontype, PermissionState, Grantee
        if ($objPermission)
        {
            foreach ($rp in $objPermission)
            {
                $spcontent = $rp.PermissionState.tostring() + " " + $rp.permissiontype.tostring() + " ON [" + $rp.objectschema + "].[" + $rp.objectname + "] TO [" + $rp.grantee + "]"
                ECHO $spcontent
            }
        
        }
    

    SP:

    alter procedure get_PogramStript_by_PowerShell
            @ServerName nvarchar(255) 
           ,@dbName nvarchar(255) 
           ,@SchemaName nvarchar(255) 
           ,@ObjectName nvarchar(255) 
           ,@ObjectType nvarchar(10) 
           ,@isCreate bit = 1
           ,@IncludePermission bit = 0
           ,@txt NVARCHAR(MAX) OUTPUT
    
    as
    
    BEGIN
    
        SET NOCOUNT ON;
    
        --@ObjectType:
        --P = SQL Stored Procedure
        --PC = Assembly (CLR) stored-procedure
        --RF = Replication-filter-procedure
        --V = View
        --AF = Aggregate function (CLR)
        --FN = SQL scalar function
        --FS = Assembly (CLR) scalar-function
        --FT = Assembly (CLR) table-valued function
        --IF = SQL inline table-valued function
        --TF = SQL table-valued-function
        --F - function, added it myself, so as not to take a steam bath if necessary and just pass that the function is needed
        
        
        DECLARE @cmd VARCHAR(8000) --here we collect the powershell script
               ,@ObjectTypePS varchar(100) --we explain PS what kind of program object is needed
               ,@HeaderOperation varchar(10) --need ALTER or CREATE script
               
    
        SELECT @ObjectTypePS = CASE WHEN @ObjectType in ('P','PC','RF') THEN 'StoredProcedures'
                                    WHEN @ObjectType in ('F','AF','FN','FS','FT','IF','TF') THEN 'UserDefinedFunctions'
                                    WHEN @ObjectType = 'V' THEN 'Views'
                               END
              ,@HeaderOperation = CASE WHEN @isCreate = 1 THEN '$false'
                                       ELSE '$true'
                                  END
    
        --unfortunately I could not figure out how to transfer the script not in one line and not in a separate ps1 file
        SET @cmd = 'powershell.exe -c " try {[System.Reflection.Assembly]::LoadWithPartialName(''Microsoft.SqlServer.SMO'') | out-null; '
        SET @cmd += '$srv = new-object Microsoft.SqlServer.Management.Smo.Server('''+@ServerName+'''); '
        SET @cmd += '$db = $srv.Databases.Item('''+@dbName+'''); '
        SET @cmd += '$obj = $db.'+@ObjectTypePS+' | where { $_.Schema -eq '''+@SchemaName+''' -and $_.Name -eq '''+@ObjectName+'''}; '
        SET @cmd += '$retval = $obj.ScriptHeader('+@HeaderOperation+') + $obj.TextBody; '
        SET @cmd += 'ECHO $retval.ToString(); ' 
    
        IF @IncludePermission = 1
        BEGIN
            SET @cmd += 'ECHO ''GO''; '
            SET @cmd += '$objPermission=$obj.EnumObjectPermissions() | Select-Object objectschema, objectname, permissiontype, PermissionState, Grantee; '
            SET @cmd += 'if ($objPermission)'
            SET @cmd += '{'
            SET @cmd += '   foreach ($rp in $objPermission)'
            SET @cmd += '   {'
            SET @cmd += '       $spcontent = $rp.PermissionState.tostring() + '' '' + $rp.permissiontype.tostring() + '' ON ['' + $rp.objectschema + ''].['' + $rp.objectname + ''] TO ['' + $rp.grantee + ''];'';'
            SET @cmd += '       ECHO $spcontent;'
            SET @cmd += '   }'
            SET @cmd += '}'
        END
        SET @cmd += '} catch {echo ''error''} "'
    
    
        declare @Tab table (line NVARCHAR(MAX))
        insert into @tab
        EXEC master.dbo.xp_cmdshell @cmd
        
        select @txt = N'USE ['+@dbName+']
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    '+(Select STRING_AGG(line,'
    ') from @tab)
    
        return  
    
    END