SMO? Scripting of all SQL stored procedures with if exists drop then create and no sp_executesql

Question

I have a seemingly simple request. I need to script all the stored procedures in a database following this criteria.

The scripts need to include dropping if the proc exists like follows...

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 this...
```
EXEC dbo.sp_executesql @statement = '....'
```
I need a separate script file for each stored procedure. So it would be [stored procedure name].sql

I notice when I try the built in sql generate scripts I can get the procs in a separate file via the checkbox for scripting objects in separate files and I can also get the if exists drop. However, it uses sp_executesql which they don't want.

So I spent a bit trying SMO and found similar issues...

A. Sadly the following just scripts only the drop statements. No way I see to combine it with the create. So I can get the separate files and no sp_executesql but I'm still missing #1 above

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

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

    scripter.Options.IncludeIfNotExists = true;

C. Finally, I can add the text manually. It successfully is set in the TextHeader. However, the 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 this is all that strange of a thing to do so I'm wondering how people are accomplishing this??? Sad if needing to create the separate files using the sql - tasks - generate scripts, followed by an app to clear out the unwanted "EXEC dbo.sp_executesql @statement = N'"

gist.github.com/3146231 - Here's the version I ended up with in case someone needs to do the same. Thanks again for a great base to work from @Akos — Dan
– Dan, Commented Jul 19, 2012 at 19:41
You can use Sql Server Management Studio "Generate Scripts" option. — QrystaL
– QrystaL, Commented Nov 13, 2012 at 10:15

Akos Lukacs · Accepted Answer · 2012-07-08 22:10:16Z

2

The following code samples are in PowerShell, but uses SMO, so you can easily transform it to c#.

Scripter felt a bit slow, so i'm using a slightly unorthodox method, but this worked for hundreds of times for me.

$sp is a stored procedure in your db, just foreaching 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 prints out some extra info:

print 'creating "{1}"...' put it in there because one time the creation just hung for minutes. Knowing the current SP name lead us to the source of the problem: connection to a linked server was down...
SP creation and last modified date, but thats not really needed.

This method does not takes script dependencies into account, so you will get a warning message in the console like "The current SP xy depends SP dsa, but creating it anyway". But runs faster because of not working out those dependencies. Worked OF for me all the time...

Uploaded the full script, and an example output to GitHub: SqlScriptExport.ps1

This one does a few extra things:

script only SPs that start with a given prefix (or all, if the prefix is empty)
scripts views as well, same filtering for prefixes.

edited Jul 8, 2012 at 22:10

answered Jul 8, 2012 at 10:20

Akos Lukacs

2,0471 gold badge17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Over a year ago

Can you post the complete script or link to it? I don't mind using a powershell version instead. Based on my additional comments on the original question I don't think scripter can be used anyway with modifications to TextHeader

Akos Lukacs Over a year ago

Code, and an example output sql script up on GitHub. This saves everything to one file, but should be pretty easy to change it to create one file per SP.

Dan Over a year ago

Most excellent reference for me. I tweaked quite a bit to get it as I needed it but that was a great help to work off of. Thank you!

Collectives™ on Stack Overflow

SMO? Scripting of all SQL stored procedures with if exists drop then create and no sp_executesql

1 Answer 1

Script drop: Just use String.Format :)

Script body

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Script drop: Just use String.Format :)

Script body

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related