Getting program module scripts using PowerShell

Question

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 

Answer 1

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