generation of sql for an alter column etc

Hi.

I have a database I need to supply something (I'm assuming a t-sql script..
maybe something else is better) to update customer tables with.
The operations include mostly changing varchar lengths, though a couple of
columns were renamed.

I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer
to generate the scripts.

I can't just send alter table scripts because I'm involving all sorts of
constraints that have to be disabled/or dropped, the alter made, then have
them enabled/ or re-created.

Basically I'm hoping to get the tools to do the rather large amount of work
for me. I'm targetting sql server 2000.

Can someone make a knowledgeable suggestion?

Regards
Jeff KishJeff Kish wrote:
> Hi.
> I have a database I need to supply something (I'm assuming a t-sql script..
> maybe something else is better) to update customer tables with.
> The operations include mostly changing varchar lengths, though a couple of
> columns were renamed.

Not a good idea IMHO although you can use sp_rename.

> I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer
> to generate the scripts.
> I can't just send alter table scripts because I'm involving all sorts of
> constraints that have to be disabled/or dropped, the alter made, then have
> them enabled/ or re-created.

Then generate the SQL for the target state and insert drops yourself.

> Basically I'm hoping to get the tools to do the rather large amount of work
> for me. I'm targetting sql server 2000.
> Can someone make a knowledgeable suggestion?

I don't think you will be able to get this out of EM - at least not
directly. It would basically mean to trace your operations and generate
SQL from that. I don't think EM will do that for such a complex set of
operations. You'll have to do some manual work.

Kind regards

robert|||Jeff Kish (jeff.kish@.mro.com) writes:
> I have a database I need to supply something (I'm assuming a t-sql
> script.. maybe something else is better) to update customer tables with.
> The operations include mostly changing varchar lengths, though a couple
> of columns were renamed.
> I'd like to maybe figure out how to get Enterprise Manager or Query
> Analyzer to generate the scripts.
> I can't just send alter table scripts because I'm involving all sorts of
> constraints that have to be disabled/or dropped, the alter made, then have
> them enabled/ or re-created.
> Basically I'm hoping to get the tools to do the rather large amount of
> work for me. I'm targetting sql server 2000.

Composing change scripts for production environments is not a task to
take lightly. Particularly not if you have to apply them while the system
is operating. (If the system closes for business, you may be able to repair
a disaster by restorin a backup.)

It requires good understanding of what can go wrong, and how to prevent
that. For instance, if you need to drop constraints to alter a column,
you should probably wrap that in a transaction, so you don't end up with
losing the constraint.

At the same time, ALTER operations that require changes to the physical
data pages, can take a huge toll on the transaction log, causing it to
grow rapidly. (Changing varchar lengths should be metadata so that should
be safe.)

You can use Enterprise Manager to have it to generate change scripts.
However, there are several flaw in those scripts, and you need to review
them carefully, and also make several changes to them. For instance, the
transaction scope in those scripts are wacko.

What may prove to be a show-stopper is that EM works with SQL 6.5 as its
target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
update as create new table and copy data over. Which sometimes is the right
thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.

There are other tools on the market. Red Gate's SQL Compare get a lot
of positive acclaim, but I have not used it myself.

One potential problem is that you don't know the name of the constraints,
because they were added without a name, so all there is a system-generated
name. In this case, you need to retrieve the name, and then run DROP
CONSTRAINT dynamically. I would suggest that you restore the constraints
with a given name.

Speaking of constraints, make sure that you re-add them WITH CHECK. The
script from EM uses WITH NOCHEK, which means that they are not checked. This
is a lot faster, but it also means that the optimizer will neglect them,
which can have expensive consequences.

Finally, before you run in production, test on a copy of production!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Fri, 24 Mar 2006 13:45:19 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Jeff Kish (jeff.kish@.mro.com) writes:
>> I have a database I need to supply something (I'm assuming a t-sql
<snip>
>Composing change scripts for production environments is not a task to
>take lightly. Particularly not if you have to apply them while the system
>is operating. (If the system closes for business, you may be able to repair
>a disaster by restorin a backup.)
>It requires good understanding of what can go wrong, and how to prevent
>that. For instance, if you need to drop constraints to alter a column,
>you should probably wrap that in a transaction, so you don't end up with
>losing the constraint.
ahh.. I had no idea that transactions could wrap/rollback ddl. I don't think
that is the case in Oracle.
>At the same time, ALTER operations that require changes to the physical
>data pages, can take a huge toll on the transaction log, causing it to
>grow rapidly. (Changing varchar lengths should be metadata so that should
>be safe.)
>You can use Enterprise Manager to have it to generate change scripts.
>However, there are several flaw in those scripts, and you need to review
>them carefully, and also make several changes to them. For instance, the
>transaction scope in those scripts are wacko.
Can you tell me how? I'm having some problem seeing how to get the equivalent
alter table etc scripts out of EM. I looked, honest. I'll even look some more.

>What may prove to be a show-stopper is that EM works with SQL 6.5 as its
>target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
>have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
>update as create new table and copy data over. Which sometimes is the right
>thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.
target is only sql server 2000 right now.
>There are other tools on the market. Red Gate's SQL Compare get a lot
>of positive acclaim, but I have not used it myself.
>One potential problem is that you don't know the name of the constraints,
>because they were added without a name, so all there is a system-generated
>name. In this case, you need to retrieve the name, and then run DROP
>CONSTRAINT dynamically. I would suggest that you restore the constraints
>with a given name.
mmm not sure I understand.. they are originally added specifically. can I just
disable them or do I need to drop them?

>Speaking of constraints, make sure that you re-add them WITH CHECK. The
>script from EM uses WITH NOCHEK, which means that they are not checked. This
>is a lot faster, but it also means that the optimizer will neglect them,
>which can have expensive consequences.
>Finally, before you run in production, test on a copy of production!
of course! the scars I have should remind me of that. :> )

thanks so much.
Jeff Kish|||Jeff Kish (jeff.kish@.mro.com) writes:
>>You can use Enterprise Manager to have it to generate change scripts.
>>However, there are several flaw in those scripts, and you need to review
>>them carefully, and also make several changes to them. For instance, the
>>transaction scope in those scripts are wacko.
>>
> Can you tell me how? I'm having some problem seeing how to get the
> equivalent alter table etc scripts out of EM. I looked, honest. I'll
> even look some more.

Right-click table and select Modify Table. As I said, it is not likely
it will generate ALTER TABLE commands, those you will have to write
yourself. But at least you will get some code to recreate constraints.
Just be sure to change WITH NOCHECK to WITH CHECK.

>>One potential problem is that you don't know the name of the constraints,
>>because they were added without a name, so all there is a system-generated
>>name. In this case, you need to retrieve the name, and then run DROP
>>CONSTRAINT dynamically. I would suggest that you restore the constraints
>>with a given name.
>>
> mmm not sure I understand.. they are originally added specifically. can
> I just disable them or do I need to drop them?

The error message from ALTER TABLE makes it clear that you have to
drop the constraint. Keep in mind that a FK column must match the
PK column it refers to, so if you change a PK/FK pair, you need to
drop constraints for both columns before changing.

Yes, it follows that from what I say that you should mainly generate
the script manually. This may seem like a crazy idea, but since it's
so important that you understand what the script does, it can be
dangerous to leave that in the hands of a tool.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Generating Create Alter Drop Statements

Im using SQL Server 2005 Standard and am trying to get T-SQL to do something that can be done easily through the UI, Genernate SQL Scripts for Create Alter Drop for objects. In the SQL manager its as easy a right clicking a DB Object and going to tasks.

Im wondering if there is a way of doing that through T-SQL.

Thanks in advance!

There is no easy way to do the same from TSQL. Best is to use SMO or SSIS to script out the objects. You could use the built-in metadata function OBJECT_DEFINITION or sp_helptext system stored procedure or query system catalog views. But you will have to code lot of the logic to determine dependencies, generate drop statements or ALTER TABLE etc. So it is best to use one of the methods above.

generating alter table script

Hello,

I need to change collation in my database (more databases acctualy).
Therefore, I wanted to make a script, which will do it at one more
time.

I already have a cursor, updating collation on all tables (fields) in
database.
The problem is, before I will to update the collations, I need to drop
all constrains and pk's.
But I do not want to erase them. After the collation will be updated,
these should be restored.
Therefore I wanted to additionaly script all cs, pk's for all tables,
drop them, and after updateing the collation, update the tables.

Does enyone have an idea how to do that? Or how can I get (in tsql) an
string containing information like:

ALTER TABLE [dbo].[PrmUserGroup] ADD
CONSTRAINT [FK_PrmUserGroup_PrmGroup] FOREIGN KEY
(
[id_group]
) REFERENCES [dbo].[PrmGroup] (
[id]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_PrmUserGroup_PrmUser] FOREIGN KEY
(
[id_user]
) REFERENCES [dbo].[PrmUser] (
[id]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

If i can have such a part of script (it will complete script of cs and
pk's) i could store it temporary in the table, and after updating a
collation, run them with dynamic sql.

Or maybe there is any other, better way?

Thank you in advance

MateuszMatik (marzec@.sauron.xo.pl) writes:
> I need to change collation in my database (more databases acctualy).
> Therefore, I wanted to make a script, which will do it at one more
> time.
> I already have a cursor, updating collation on all tables (fields) in
> database.
> The problem is, before I will to update the collations, I need to drop
> all constrains and pk's.
> But I do not want to erase them. After the collation will be updated,
> these should be restored.
> Therefore I wanted to additionaly script all cs, pk's for all tables,
> drop them, and after updateing the collation, update the tables.
> Does enyone have an idea how to do that? Or how can I get (in tsql) an
> string containing information like:

I would rather build a new database from scripts, and then use BCP to
bulk data out and in. You can easily script the BCP commands from
sysobjects. Don't forget to include a condition to get -E for tables
with the IDENTITY property.

Best is to apply constraints after the load, but you can load them as
NOCHECK and the enable them with CHECK WITH CHECK. (That's a horrible
syntax, but it's important to actually verify the constraints when you
enable them, or else the optimizer cannot make use of them.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

generating ALTER statement with SMO

Is it possible to generate ALTER statement for the stored proc with the SMO?

Any link will be appreciated. Thanks.

Any help please. Or it is not possible at all.|||In practice I always drop an existing stored procedure and recreate it when a change is required. Is there a particular reason why you'd rather alter the procedure?

generating ALTER statement with SMO

Is it possible to generate ALTER statement for the stored proc with the SMO?

Any link will be appreciated. Thanks.

Any help please. Or it is not possible at all.|||In practice I always drop an existing stored procedure and recreate it when a change is required. Is there a particular reason why you'd rather alter the procedure?

generate the alter table script

hi,
i want to know how can i generate the alter table script from a tool?
thanks!
regards,
mullinHi,
How can we get an Alter table script with out mentioning
the schema changes.
Enterprise manager also help you to look the script after changing the table
design in design view. The script will be executed when we click Yes.
The same thing can done using other 3rd party tools like DBARTISAN , etc...
Thanks
Hri
MCDBA
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:#zR#6w#BEHA.2556@.TK2MSFTNGP12.phx.gbl...
> hi,
> i want to know how can i generate the alter table script from a tool?
> thanks!
> regards,
> mullin
>|||take a look at www.dbghost.com

Generate ALTER scripts

Hi. In SSMS (9.00.3042) I'm trying to generate a single file containing
ALTER statements for all the SPs, views and UDFs in a database. In the
Generate Scripts wizard, I've only been able to generate create and drop
statements. Any tips are appreciated.
Thanks,
Ben
I found a suggestion on Connect for this:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
Unfortunately, Microsoft closed the suggestion last month without fixing it.
Their comments was ... "Alter scripting post multi select isnt supported and
object alter scripts in the UI isnt going to be easy to manage." I'm not
sure what would be so difficult.
Ben
|||Ben
What if you script out all objects and just REPLACE 'create' with 'alter'
( i.e in notepad or something)?
"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
> Ben
|||"Uri Dimant" <urid@.iscar.co.il> wrote: in message
news:ONtFmVPjIHA.748@.TK2MSFTNGP04.phx.gbl...

> Ben
> What if you script out all objects and just REPLACE 'create' with 'alter'
> ( i.e in notepad or something)?
Hi Uri. I looked at doing that once, but noticed the word "create" was also
in the name of some SPs (e.g. CreateRecord) and in some comments within some
of these objects. I also looked at replacing "CREATE PROC" with "ALTER
PROC", but then I saw how a good number of the procedures have multiple
spaces between CREATE and PROC !! This might be possible to do, however, if
I came up with a regular expression and did a search/replace in Notepad++
(or equivalent editor). Whatever I do, I just want to make sure it's not
too difficult to be able to repeat as I may need to regenerate the ALTER
statements again.
Thank you,
Ben
|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:

> This might be possible to do, however, if I came up with a regular
> expression and did a search/replace in Notepad++ (or equivalent editor).
These regular expressions work in Notepad++:
^\s*(create)\s+(proc)
^\s*(create)\s+(view)
^\s*(create)\s+(function)
Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
between the CREATE word and the following word (proc, view or function).
The only thing it doesn't take into account would be if CREATE and PROC were
on two different lines. But, I don't think I have any of those.
|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
There is hope yet (but not for SQL Server 2008). Here is a suggestion I
submitted almost a year ago, and it is still active:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273938
|||You can always write a little script in any language that supports regular
expressions to change CRAETE PROC (or a combo of all the allowed syntactic
formats) to ALTER PROC. This is a much better approach than demanding a GUI
for every little possible syntactic format.
Linchi
"Ben Amada" wrote:

> "Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
>
> These regular expressions work in Notepad++:
> ^\s*(create)\s+(proc)
> ^\s*(create)\s+(view)
> ^\s*(create)\s+(function)
> Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
> between the CREATE word and the following word (proc, view or function).
> The only thing it doesn't take into account would be if CREATE and PROC were
> on two different lines. But, I don't think I have any of those.
>
|||"Linchi Shea" wrote:

> You can always write a little script in any language that supports regular
> expressions to change CRAETE PROC (or a combo of all the allowed syntactic
> formats) to ALTER PROC. This is a much better approach than demanding a
> GUI
> for every little possible syntactic format.
Hi. In the long run, you're probably right ... a single script or program
to do all the conversions in one shot would be best. The regular
expressions I posted a little earlier should account for all the variations
I have to deal with. I just have to do a search & replace 3 times (once for
each object type). Heck, I could have put together a small .NET app to
replace Create with Alter in less time than it took me to come up with the
regular expression :-)
|||Hello Ben,

> "Linchi Shea" wrote:
> Hi. In the long run, you're probably right ... a single script or
> program to do all the conversions in one shot would be best. The
> regular expressions I posted a little earlier should account for all
> the variations I have to deal with. I just have to do a search &
> replace 3 times (once for each object type). Heck, I could have put
> together a small .NET app to replace Create with Alter in less time
> than it took me to come up with the regular expression :-)
You can even use the following syntax:
^\s*create\s+(proc|table|trigger|...)
and replace it with:
ALTER $1
which should allow you to search and replace in one pass
Jesse Houwing
jesse.houwing at sogeti.nl
|||"Jesse Houwing" wrote:

> You can even use the following syntax:
> ^\s*create\s+(proc|table|trigger|...)
> and replace it with:
> ALTER $1
> which should allow you to search and replace in one pass
That's really slick Jesse ... thanks!

Generate ALTER scripts

Hi. In SSMS (9.00.3042) I'm trying to generate a single file containing
ALTER statements for all the SPs, views and UDFs in a database. In the
Generate Scripts wizard, I've only been able to generate create and drop
statements. Any tips are appreciated.
Thanks,
BenI found a suggestion on Connect for this:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
Unfortunately, Microsoft closed the suggestion last month without fixing it.
Their comments was ... "Alter scripting post multi select isnt supported and
object alter scripts in the UI isnt going to be easy to manage." I'm not
sure what would be so difficult.
Ben|||Ben
What if you script out all objects and just REPLACE 'create' with 'alter'
( i.e in notepad or something)?
"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
> Ben|||"Uri Dimant" <urid@.iscar.co.il> wrote: in message
news:ONtFmVPjIHA.748@.TK2MSFTNGP04.phx.gbl...
> Ben
> What if you script out all objects and just REPLACE 'create' with 'alter'
> ( i.e in notepad or something)?
Hi Uri. I looked at doing that once, but noticed the word "create" was also
in the name of some SPs (e.g. CreateRecord) and in some comments within some
of these objects. I also looked at replacing "CREATE PROC" with "ALTER
PROC", but then I saw how a good number of the procedures have multiple
spaces between CREATE and PROC !! This might be possible to do, however, if
I came up with a regular expression and did a search/replace in Notepad++
(or equivalent editor). Whatever I do, I just want to make sure it's not
too difficult to be able to repeat as I may need to regenerate the ALTER
statements again.
Thank you,
Ben|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
> This might be possible to do, however, if I came up with a regular
> expression and did a search/replace in Notepad++ (or equivalent editor).
These regular expressions work in Notepad++:
^\s*(create)\s+(proc)
^\s*(create)\s+(view)
^\s*(create)\s+(function)
Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
between the CREATE word and the following word (proc, view or function).
The only thing it doesn't take into account would be if CREATE and PROC were
on two different lines. But, I don't think I have any of those.|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
There is hope yet (but not for SQL Server 2008). Here is a suggestion I
submitted almost a year ago, and it is still active:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273938|||You can always write a little script in any language that supports regular
expressions to change CRAETE PROC (or a combo of all the allowed syntactic
formats) to ALTER PROC. This is a much better approach than demanding a GUI
for every little possible syntactic format.
Linchi
"Ben Amada" wrote:
> "Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
> > This might be possible to do, however, if I came up with a regular
> > expression and did a search/replace in Notepad++ (or equivalent editor).
> These regular expressions work in Notepad++:
> ^\s*(create)\s+(proc)
> ^\s*(create)\s+(view)
> ^\s*(create)\s+(function)
> Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
> between the CREATE word and the following word (proc, view or function).
> The only thing it doesn't take into account would be if CREATE and PROC were
> on two different lines. But, I don't think I have any of those.
>|||"Linchi Shea" wrote:
> You can always write a little script in any language that supports regular
> expressions to change CRAETE PROC (or a combo of all the allowed syntactic
> formats) to ALTER PROC. This is a much better approach than demanding a
> GUI
> for every little possible syntactic format.
Hi. In the long run, you're probably right ... a single script or program
to do all the conversions in one shot would be best. The regular
expressions I posted a little earlier should account for all the variations
I have to deal with. I just have to do a search & replace 3 times (once for
each object type). Heck, I could have put together a small .NET app to
replace Create with Alter in less time than it took me to come up with the
regular expression :-)

Generate Alter Script

Hi,

I read some related threads but none seem to answer my question. How to script a udf or a store procedure that exists in my database as an ALTER script. I don't want to modify the udf to be able to capture it(captureSql). I just want to script it as an ALTER. The scriptingOptions contains the DROP but I did not see the ALTER. I know SSMS does that when I right click on a store procedure I have the option to script it as ALTER.

I am guessing I can get the script then replace the CREATE with ALTER but that does not look to me a good solution. I am hoping there is something in SMO I can use.

Thanks,
Elie

Source Code Sample:

Code Block

using System;

using System.Configuration;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

using System.Threading;

using System.Collections.Specialized;

using System.IO;

using System.Text.RegularExpressions;

namespace SMOEvents

{

class SmoEvents

{

private static Server server = null;

private static Database db = null;

private static string folderPath = "";

private static string procedureNamePattern = "";

private static string fileNameExnention = "";

static void Main(string[] args)

{

#region config data

string connString = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None).ConnectionStrings.ConnectionStrings["MSSqlServer"].ToString();

System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(connString);

if (!builder.IntegratedSecurity)

{

SqlConnectionInfo ci = new SqlConnectionInfo(builder.DataSource, builder.UserID, builder.Password);

ServerConnection serverConn = new ServerConnection(ci);

server = new Server(serverConn);

}

else

{

server = new Server(builder.DataSource);

}

if (builder.InitialCatalog != "")

{

db = server.Databases[builder.InitialCatalog];

}

else {

db = server.Databases[0];

}

procedureNamePattern = ConfigurationManager.AppSettings.GetValues("ProcedureNamePattern").GetValue(0).ToString();

folderPath = ConfigurationManager.AppSettings.GetValues("LocalFolderPath").GetValue(0).ToString();

fileNameExnention = ConfigurationManager.AppSettings.GetValues("FileNameExnention").GetValue(0).ToString();

#endregion

if (args.Length > 0)

{

if (args[0].ToString() == "ListenEvents")

{

ListenEvents();

}

else

{

ScriptAllProcedures();

return;

}

}

else {

ScriptAllProcedures();

return;

}

}

private static void ScriptAllProcedures()

{

Regex rx = new Regex(procedureNamePattern, RegexOptions.Compiled | RegexOptions.IgnoreCase);

foreach (StoredProcedure sp in db.StoredProcedures)

{

if ((!sp.IsEncrypted) && (!sp.IsSystemObject) && (rx.IsMatch(sp.Name)))

{

string fileName = folderPath + sp.Schema + "." + sp.Name + fileNameExnention;

ScriptProcedure(sp, fileName);

}

}

}

private static void ScriptProcedure(StoredProcedure sp, string fileName)

{

FileInfo fi = new FileInfo(fileName);

if ((!fi.Exists) ||(fi.Exists && !fi.IsReadOnly))

{

string body = "IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + sp.Schema + "].[" + sp.Name + "]') AND type in (N'P', N'PC'))";

body = body + "\r\n" + " DROP PROCEDURE [" + sp.Schema + "].[" + sp.Name + "]";

body = body + "\r\n" + "GO\n";

body = body + "\r\n" + "SET ANSI_NULLS ON";

body = body + "\r\n" + "GO";

body = body + "\r\n" + "SET QUOTED_IDENTIFIER ON";

body = body + "\r\n" + "GO";

body = body + "\r\n";

body = body + "\r\n" + sp.TextHeader;

body = body + "\r\n" + sp.TextBody;

body = body + "\r\n" + "GO";

body = body + "\r\n";

body = body + "\r\n" + "SET ANSI_NULLS OFF";

body = body + "\r\n" + "GO";

body = body + "\r\n" + "SET QUOTED_IDENTIFIER OFF";

body = body + "\r\n" + "GO";

File.WriteAllText(fileName, body, UnicodeEncoding.Unicode);

Console.WriteLine(@."{0} ", fileName);

}

}

private static void ListenEvents()

{

db.Events.ServerEvent += new ServerEventHandler(Events_ServerEvent);

// DDL events

db.Events.SubscribeToEvents(DatabaseEvent.AlterProcedure + DatabaseEvent.CreateProcedure);

Console.WriteLine(@."Starting events for server: {0}", server.Name);

Console.WriteLine();

Console.WriteLine(@."(Hit Ctrl-C or Ctrl-Break to quit.)");

//Console.WriteLine(@."Press any key to continue.");

//Console.ReadKey();

Console.WriteLine(@."Start listening: {0}", DateTime.Now);

// Start receiving events

db.Events.StartEvents();

// Wait indefinitely for events to occur

Thread.Sleep(Timeout.Infinite);

Console.WriteLine(@."Stop listening ...");

// Unsubscribe from all the events when finished

server.Events.UnsubscribeAllEvents();

}

/// <summary>

/// to avoid Access Denied Error you should execute the following script:

/// ALTER DATABASE [@.dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

/// go

/// ALTER DATABASE [@.dbname] SET ENABLE_BROKER

/// go

/// ALTER DATABASE [@.dbname] SET MULTI_USER

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

static void Events_ServerEvent(object sender, ServerEventArgs e)

{

try

{

if (e.Properties["ObjectName"] != null)

{

StoredProcedure sp = db.StoredProcedures[e.Properties["ObjectName"].Value.ToString()];

if ((!sp.IsEncrypted) && (!sp.IsSystemObject))

{

string fileName = folderPath + sp.Schema + "." + sp.Name + fileNameExnention;

ScriptProcedure(sp, fileName);

}

}

Console.WriteLine(@."EventType: {0, -20} SPID: {1, 4} PostTime: {2, -20}", e.EventType, e.Spid, e.PostTime);

Console.WriteLine();

}

catch (ApplicationException ex)

{

Console.WriteLine(ex.ToString());

}

}

}

}

App.Config Sample:

Code Block

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

<appSettings>

<add key="ProcedureNamePattern" value=".+"/>

<add key="FileNameExnention" value=".sql"/>

<add key="LocalFolderPath" value=".\Stored Procedures\"/>

</appSettings>

<connectionStrings>

<add name="MSSqlServer" connectionString="Data Source=.;Integrated Security=true;Initial Catalog=AdventureWorks;" providerName="System.Data.SqlClient" />

</connectionStrings>

</configuration>

|||

Thanks for the code.

I tried the code with the Listenevents option but I am getting an error: ProviderLoadFailure

on this line db.Events.StartEvents();

The ScriptProcedure() does script it as create. But I am looking for a way to be scripted as Alter.

Thanks

Elie

|||

Scripting with CREATE and replacing the CREATE with ALTER I believe is the only way you can get it done with SMO. There is not a native ALTER creation in SMO (that is documented).

|||

Thanks I thought so. I wish I found the documentation.

Elie

Generate Alter Script

Hi,

I read some related threads but none seem to answer my question. How to script a udf or a store procedure that exists in my database as an ALTER script. I don't want to modify the udf to be able to capture it(captureSql). I just want to script it as an ALTER. The scriptingOptions contains the DROP but I did not see the ALTER. I know SSMS does that when I right click on a store procedure I have the option to script it as ALTER.

I am guessing I can get the script then replace the CREATE with ALTER but that does not look to me a good solution. I am hoping there is something in SMO I can use.

Thanks,
Elie

Source Code Sample:

Code Block

using System;

using System.Configuration;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

using System.Threading;

using System.Collections.Specialized;

using System.IO;

using System.Text.RegularExpressions;

namespace SMOEvents

{

class SmoEvents

{

private static Server server = null;

private static Database db = null;

private static string folderPath = "";

private static string procedureNamePattern = "";

private static string fileNameExnention = "";

static void Main(string[] args)

{

#region config data

string connString = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None).ConnectionStrings.ConnectionStrings["MSSqlServer"].ToString();

System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(connString);

if (!builder.IntegratedSecurity)

{

SqlConnectionInfo ci = new SqlConnectionInfo(builder.DataSource, builder.UserID, builder.Password);

ServerConnection serverConn = new ServerConnection(ci);

server = new Server(serverConn);

}

else

{

server = new Server(builder.DataSource);

}

if (builder.InitialCatalog != "")

{

db = server.Databases[builder.InitialCatalog];

}

else {

db = server.Databases[0];

}

procedureNamePattern = ConfigurationManager.AppSettings.GetValues("ProcedureNamePattern").GetValue(0).ToString();

folderPath = ConfigurationManager.AppSettings.GetValues("LocalFolderPath").GetValue(0).ToString();

fileNameExnention = ConfigurationManager.AppSettings.GetValues("FileNameExnention").GetValue(0).ToString();

#endregion

if (args.Length > 0)

{

if (args[0].ToString() == "ListenEvents")

{

ListenEvents();

}

else

{

ScriptAllProcedures();

return;

}

}

else {

ScriptAllProcedures();

return;

}

}

private static void ScriptAllProcedures()

{

Regex rx = new Regex(procedureNamePattern, RegexOptions.Compiled | RegexOptions.IgnoreCase);

foreach (StoredProcedure sp in db.StoredProcedures)

{

if ((!sp.IsEncrypted) && (!sp.IsSystemObject) && (rx.IsMatch(sp.Name)))

{

string fileName = folderPath + sp.Schema + "." + sp.Name + fileNameExnention;

ScriptProcedure(sp, fileName);

}

}

}

private static void ScriptProcedure(StoredProcedure sp, string fileName)

{

FileInfo fi = new FileInfo(fileName);

if ((!fi.Exists) ||(fi.Exists && !fi.IsReadOnly))

{

string body = "IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + sp.Schema + "].[" + sp.Name + "]') AND type in (N'P', N'PC'))";

body = body + "\r\n" + " DROP PROCEDURE [" + sp.Schema + "].[" + sp.Name + "]";

body = body + "\r\n" + "GO\n";

body = body + "\r\n" + "SET ANSI_NULLS ON";

body = body + "\r\n" + "GO";

body = body + "\r\n" + "SET QUOTED_IDENTIFIER ON";

body = body + "\r\n" + "GO";

body = body + "\r\n";

body = body + "\r\n" + sp.TextHeader;

body = body + "\r\n" + sp.TextBody;

body = body + "\r\n" + "GO";

body = body + "\r\n";

body = body + "\r\n" + "SET ANSI_NULLS OFF";

body = body + "\r\n" + "GO";

body = body + "\r\n" + "SET QUOTED_IDENTIFIER OFF";

body = body + "\r\n" + "GO";

File.WriteAllText(fileName, body, UnicodeEncoding.Unicode);

Console.WriteLine(@."{0} ", fileName);

}

}

private static void ListenEvents()

{

db.Events.ServerEvent += new ServerEventHandler(Events_ServerEvent);

// DDL events

db.Events.SubscribeToEvents(DatabaseEvent.AlterProcedure + DatabaseEvent.CreateProcedure);

Console.WriteLine(@."Starting events for server: {0}", server.Name);

Console.WriteLine();

Console.WriteLine(@."(Hit Ctrl-C or Ctrl-Break to quit.)");

//Console.WriteLine(@."Press any key to continue.");

//Console.ReadKey();

Console.WriteLine(@."Start listening: {0}", DateTime.Now);

// Start receiving events

db.Events.StartEvents();

// Wait indefinitely for events to occur

Thread.Sleep(Timeout.Infinite);

Console.WriteLine(@."Stop listening ...");

// Unsubscribe from all the events when finished

server.Events.UnsubscribeAllEvents();

}

/// <summary>

/// to avoid Access Denied Error you should execute the following script:

/// ALTER DATABASE [@.dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

/// go

/// ALTER DATABASE [@.dbname] SET ENABLE_BROKER

/// go

/// ALTER DATABASE [@.dbname] SET MULTI_USER

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

static void Events_ServerEvent(object sender, ServerEventArgs e)

{

try

{

if (e.Properties["ObjectName"] != null)

{

StoredProcedure sp = db.StoredProcedures[e.Properties["ObjectName"].Value.ToString()];

if ((!sp.IsEncrypted) && (!sp.IsSystemObject))

{

string fileName = folderPath + sp.Schema + "." + sp.Name + fileNameExnention;

ScriptProcedure(sp, fileName);

}

}

Console.WriteLine(@."EventType: {0, -20} SPID: {1, 4} PostTime: {2, -20}", e.EventType, e.Spid, e.PostTime);

Console.WriteLine();

}

catch (ApplicationException ex)

{

Console.WriteLine(ex.ToString());

}

}

}

}

App.Config Sample:

Code Block

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

<appSettings>

<add key="ProcedureNamePattern" value=".+"/>

<add key="FileNameExnention" value=".sql"/>

<add key="LocalFolderPath" value=".\Stored Procedures\"/>

</appSettings>

<connectionStrings>

<add name="MSSqlServer" connectionString="Data Source=.;Integrated Security=true;Initial Catalog=AdventureWorks;" providerName="System.Data.SqlClient" />

</connectionStrings>

</configuration>

|||

Thanks for the code.

I tried the code with the Listenevents option but I am getting an error: ProviderLoadFailure

on this line db.Events.StartEvents();

The ScriptProcedure() does script it as create. But I am looking for a way to be scripted as Alter.

Thanks

Elie

|||

Scripting with CREATE and replacing the CREATE with ALTER I believe is the only way you can get it done with SMO. There is not a native ALTER creation in SMO (that is documented).

|||

Thanks I thought so. I wish I found the documentation.

Elie