How to create ALTER scripts instead of CREATE scripts using SMO (Server Management Object)

Question

I am using the Microsoft.SqlServer.Management.Smo classes to script out SQL scripts for stored procedures, tables, views etc. I am doing this for putting them in source control.

Instead of the CREATE scripts for stored procedures, how can I get ALTER scripts for them? Is there an setting / option in the ScriptingOptions' properties?

As a workaround, once the scripts are created, I'm reading them, changing the CREATE to ALTER and writing them back out. — hIpPy
– hIpPy, Commented Jul 7, 2010 at 17:30

Steven Liekens · Accepted Answer · 2015-09-03 15:35:37Z

5

The StoredProcedure class has a method that creates ALTER statements.

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.storedprocedure.scriptheader(v=sql.120).aspx

Usage

var sprocHeader = sproc.ScriptHeader(forAlter: true);
var sprocBody = sproc.TextBody;

answered Sep 3, 2015 at 15:35

Steven Liekens

14.2k9 gold badges70 silver badges96 bronze badges

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

1 Comment

jyao Over a year ago

This actually is the key point for SP creating "alter" script. Thanks !

Grim · Accepted Answer · 2011-07-22 10:07:53Z

2

Use the normal scriptingoptions and tools for SMO c#

then simply use.

foreach (string line in script)
            {
                string l = line.Replace("CREATE FUNCTION", "ALTER FUNCTION");
            }

Ta Da. :).

answered Jul 22, 2011 at 10:07

Grim

372 bronze badges

Comments

hIpPy · Accepted Answer · 2010-07-09 16:22:33Z

1

There seems to be no setting for ALTER scripts. The property with ScriptingOptions.ScriptDrops as true creates the drop statements. The downside to this is that the permissions would have to reassigned.

Closing this question.

answered Jul 9, 2010 at 16:22

hIpPy

5,1758 gold badges56 silver badges73 bronze badges

1 Comment

Bobby B Over a year ago

I agree - I tried this before and never found a method for scripting out an alter. I ended up resulting to using the ScriptingOptions (msdn.microsoft.com/en-us/library/…) to specify a check for existence and drop if found.

Tim Stone · Accepted Answer · 2012-03-01 00:48:54Z

1

Have a look at DBSourceTools.

It is a GUI tool to script all objects in a SQL Database to disk, specifically for source-code control of databases.The back-end uses SMO.

edited Mar 1, 2012 at 0:48

Tim Stone

19.2k6 gold badges58 silver badges66 bronze badges

answered Jul 26, 2010 at 5:14

blorkfish

23.1k4 gold badges35 silver badges24 bronze badges

Comments

jcolebrand · Accepted Answer · 2012-03-01 01:12:00Z

0

I use this in compare between servers. toSP is the textbody of the SP. If there is none on the "TO" server I do Create() otherwise it is Alter() for the loaded up StoredProcedure spT

If toSP = "" Then  ' Empty
    spT.Create()
Else
    spT.Alter()
End If

edited Mar 1, 2012 at 1:12

jcolebrand

16k12 gold badges78 silver badges123 bronze badges

answered Jun 1, 2011 at 15:11

SteveO

4772 gold badges9 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to create ALTER scripts instead of CREATE scripts using SMO (Server Management Object)

5 Answers 5

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related