SMO? The script of all SQL stored procedures with if exists, then create and not sp_executesql

Question

I have a seemingly simple request. I need a script to save all stored procedures in a database according to these criteria.

• Scenarios should include dropping if proc exists, as shown below ...

  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myproc]') AND type in (N'P', N'PC')) 

• the script cannot have sp_executesql, so nothing like that ...

   EXEC dbo.sp_executesql @statement = '....' 

• I need a separate script file for each stored procedure. So it will be [stored procedure name] .sql

I notice that when I try to create sql generate scripts, I can get procs in a separate file using the checkbox for script objects in separate files, and I can also get drop if exists. However, it uses sp_executesql, which they do not want.

So, I spent a little effort on SMO and found similar problems ...

a. Unfortunately, the following scripts are just pop-up statements. By no means am I going to combine it with creation. Therefore, I can get separate files and not sp_executesql, but I still can’t see # 1 above

  Scripter scripter = new Scripter(); scripter.Options.ScriptDrops = true; 

B. Secondly, the following parameter changes the output to use sp_executesql

  scripter.Options.IncludeIfNotExists = true; 

C. Finally, I can add the text manually. It has been successfully installed in TextHeader. However, scripter.Script () throws an exception "{" Script failed for StoredProcedure 'dbo.myproc'. "}

  storedProcedure.TextHeader = string.Format("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{0}') AND type in (N'P', N'PC')) \r\nDROP PROCEDURE {0} \r\nGO\r\n{1}", storedProcedure.Name, storedProcedure.TextHeader); scripter.Options.FileName = Path.Combine(storedProceduresPath, storedProcedure.Name + ".sql"); scripter.Script(new Urn[] { storedProcedure.Urn }); //Exception! - Script failed for StoredProcedure 

I can’t imagine that it’s all strange, that I do so, I wonder how people achieve this ??? Sadly, if you need to create separate files using sql tasks - generate scripts and then the application to clear the unwanted "EXEC dbo.sp_executesql @statement = N"

Answer 1

The following code examples are in PowerShell, but use SMO, so you can easily convert it to C #.

The scripter felt a little slow, so I use the slightly unorthodox method, but this is hundreds of times for me.

$sp is a stored procedure in your db, just for all SPs:

 foreach($sp in $db.StoredProcedures) 

Script drop: just use String.Format :)

 $sbHead.AppendLine( [string]::Format(@" IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[{1}]') AND type in (N'P', N'PC')) GO "@, $sp.Schema, $sp.Name)) 

Script body

 $sbBody.AppendLine( [string]::Format(@" print 'creating "{1}"...' -- * * * * #{0}:{1}; CreateDate:{2}, DateLastModified:{3} * * * * GO {4} {5} GO "@, $cnt, $sp.Name, $sp.CreateDate, $sp.DateLastModified, $sp.TextHeader, $sp.TextBody)) 

This produces additional information:

• print 'creating "{1}"...' put it there, because once the creation just hung for several minutes. Knowing the current SP name will lead us to the source of the problem: the connection to the linked server was down ...
• Creation of SP and last modified date, but it really is not necessary.

This method does not take into account script dependencies, so you get a warning message in the console, for example: "The current SP xy depends on SP dsa, but creates it anyway." But it works faster due to the fact that it does not develop these dependencies. Worked for me all the time ...

Download the full script and example output to GitHub: SqlScriptExport.ps1

This does a few extra things:

• Script only SP starting with the given prefix (or all if the prefix is ​​empty)
• also displays scripts, same filtering for prefixes.