The Database is too big to implement this option,

Do you know how can I do this through PowerShell using SMO:

I started something like this but I don't know how to put on $SPtoscript the result of the query:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$server = New-Object Microsoft.SqlServer.Management.Smo.Server 'server'

$database = $server.Databases["Database"]

$Scripter = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("server")

$Scripter.Options.DriAll=$False

$Scripter.Options.IncludeHeaders=$False

$Scripter.Options.ToFileOnly=$True

$Scripter.Options.WithDependencies=$False

$Scripter.Options.FileName = "c:\StoredProcedures_Ref_server.sql"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = "SELECT Object_Name(id) as Name FROM syscomments WHERE text LIKE '%server%'"

Invoke-Sqlcmd -Query "SELECT Object_Name(id) as Name FROM syscomments WHERE text LIKE '%server%'" | Out-File -filePath

"c:\StoredProcedures_Ref_server.sql" -ServerInstance "server" -Database Database

foreach ($StoredProcName in $SPtoscript)

{

$MyStoredProc = $database.StoredProcedures["$StoredProcName"]

{

$Scripter.Options.AppendToFile=$True

$Scripter.Options.ScriptDrops=$True;

$Scripter.Options.IncludeIfNotExists=$True;

$Scripter.Script($MyStoredProc)

$Scripter.Options.ScriptDrops=$False;

$Scripter.Options.IncludeIfNotExists=$False;

$Scripter.Options.AppendToFile=$True

$Scripter.Script($MyStoredProc)

}

}

Thanks

APA