Generating SQL-Skripts

Hi,
i'm sitting in front of an obscure database containing round about 400
views. :-)
I would like to export these views into an working SQL-Skript to be able
to recreate these views on a second database with an identical
table-structure.
The problem: The views are dumped to the file in the wrong order. Do you
know
this problem? What can I do?
Thanks for any hints...
Jan
Hi,
Are you going to replicate the Table and View into the new database or just
the views.
1.
If you are going to create only the views in the new database then you have
to change the script for view creation. Because your real table will
be in the old database.
it should be some thing like
Create view V1_customer as select * from olddbname..table_name
2.
Incase if you are moving both Table and the view, then create 2 scripts. 1
for tables and the other for views.
Execute the Tables first after then run the script contains the view.
Did I answered your question completely?
Thanks
Hari
MCDBA
"Jan Schmitz" <schmitz@.face.de> wrote in message
news:Oz4KJGLGEHA.712@.tk2msftngp13.phx.gbl...
> Hi,
> i'm sitting in front of an obscure database containing round about 400
> views. :-)
> I would like to export these views into an working SQL-Skript to be able
> to recreate these views on a second database with an identical
> table-structure.
> The problem: The views are dumped to the file in the wrong order. Do you
> know
> this problem? What can I do?
> Thanks for any hints...
> Jan
>
|||check out www.dbghost.com
|||"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:%23W$dU5LGEHA.3940@.tk2msftngp13.phx.gbl...
> Hi,
> Incase if you are moving both Table and the view, then create 2 scripts. 1
> for tables and the other for views.
> Execute the Tables first after then run the script contains the view.
>
Hi,
this is what i tried. The problem ist, that the 400 Views in the
Views-Skript are dumped in the wrong order. View "B", selecting from View
"A" is created before View "A" ist created. So the generated skript simply
doesn't work. I think, what I need is a way to renew the sysdepends...
Greetings...Jan
|||"mark baekdal" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BED6BA68-89A6-42AC-A075-FF67F35D86A5@.microsoft.com...
> check out www.dbghost.com
Hi,
at first look, this sounds great - I will take a closer look at that tool...
Thanks a lot,
Jan
sql

Generating SQL Script

Hello there
I have huge database on sql server. The database probide tables, views,
store procedures and functions
Some of the views or the store procedures are depend on the functions, store
presedures are depend on the views.
When i update the version I generate SQL Script and run it on my client
The script first of all destroy objects on the client and establish the new
schema. The sql script firs create tables, views, store procedures and at
finaly functions.
Because some of the views or the store procedures are using the functions
they can't be created.
What i need to do to create first the functions?
and if on the future i will create functions that using functions. Is there
a "smart" script that first of all create the objects without any
dependencies and after that create wnat under them?
any help would be usefulI have the same problem with some scripts I generate. Here's the solutions
I came up with:
1) manually re-arrange the script so that objects that need to be created
first are created first, or
2) generate multiple scripts (generate one that just creates functions, one
that just creates views, etc.)
I prefer the second method myself, since it's less work, especially for very
large scripts.
Thx
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23blV03ZHFHA.400@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have huge database on sql server. The database probide tables, views,
> store procedures and functions
> Some of the views or the store procedures are depend on the functions,
> store
> presedures are depend on the views.
> When i update the version I generate SQL Script and run it on my client
> The script first of all destroy objects on the client and establish the
> new
> schema. The sql script firs create tables, views, store procedures and at
> finaly functions.
> Because some of the views or the store procedures are using the functions
> they can't be created.
> What i need to do to create first the functions?
> and if on the future i will create functions that using functions. Is
> there
> a "smart" script that first of all create the objects without any
> dependencies and after that create wnat under them?
> any help would be useful
>

Generating scripts, tables, views, procedures, roles...

Hi...

I'm trying to generate scripts in SQL Server Management Studio 2005.

When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.

1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?

2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?

Thanks....

Which exact error do you get ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

sorry, I cant reproduce the errors because my workbuddy has the day off...

But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'

And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?

|||

You may find that the Database Publishing Wizard offers you more functionality.

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

|||Thomas, did you find a solution to your problem?

I have the same problem and Database Publishing Wizard does not work any better.

The CREATE VIEW is placed before the dependant tables in the script.

This ONLY happens when I'm trying to script a SQL Server 2005 Express db.

I run the script wizard in SQL Server Management Studio 2005.

SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399

Thanks
Jonas
|||

Did you use the switch "create dependent objects" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Yes I did.

I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.

I can not include any image of the properties selected, but all are default exept the dependant switch which is true.

The final report start with:

Generate Script Progress

- Determining objects in database '....MDF' that will be scripted. (Success)

- dbo.AllDataView (Success)

- dbo.FindNewRawData (Success)

Which also shows that a View is created first of all objects.

Thanks

Jonas

Generating scripts, tables, views, procedures, roles...

Hi...

I'm trying to generate scripts in SQL Server Management Studio 2005.

When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.

1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?

2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?

Thanks....

Which exact error do you get ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

sorry, I cant reproduce the errors because my workbuddy has the day off...

But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'

And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?

|||

You may find that the Database Publishing Wizard offers you more functionality.

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

|||Thomas, did you find a solution to your problem?

I have the same problem and Database Publishing Wizard does not work any better.

The CREATE VIEW is placed before the dependant tables in the script.

This ONLY happens when I'm trying to script a SQL Server 2005 Express db.

I run the script wizard in SQL Server Management Studio 2005.

SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399

Thanks
Jonas
|||

Did you use the switch "create dependent objects" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Yes I did.

I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.

I can not include any image of the properties selected, but all are default exept the dependant switch which is true.

The final report start with:

Generate Script Progress

- Determining objects in database '....MDF' that will be scripted. (Success)

- dbo.AllDataView (Success)

- dbo.FindNewRawData (Success)

Which also shows that a View is created first of all objects.

Thanks

Jonas

Generating scripts, tables, views, procedures, roles...

Hi...

I'm trying to generate scripts in SQL Server Management Studio 2005.

When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.

1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?

2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?

Thanks....

Which exact error do you get ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

sorry, I cant reproduce the errors because my workbuddy has the day off...

But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'

And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?

|||

You may find that the Database Publishing Wizard offers you more functionality.

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

|||Thomas, did you find a solution to your problem?

I have the same problem and Database Publishing Wizard does not work any better.

The CREATE VIEW is placed before the dependant tables in the script.

This ONLY happens when I'm trying to script a SQL Server 2005 Express db.

I run the script wizard in SQL Server Management Studio 2005.

SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399

Thanks
Jonas
|||

Did you use the switch "create dependent objects" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Yes I did.

I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.

I can not include any image of the properties selected, but all are default exept the dependant switch which is true.

The final report start with:

Generate Script Progress

- Determining objects in database '....MDF' that will be scripted. (Success)

- dbo.AllDataView (Success)

- dbo.FindNewRawData (Success)

Which also shows that a View is created first of all objects.

Thanks

Jonas

Generating Scripts from SQL Server 2000 using DMO and C#

Hi,

I'm generate scripts from a sql server 2000 database using DMO and C#.

the probelm am having is that when i try to generate views i get views *and* tables!

i tried to get rid of the tables:

SQLDMO.Transfer2 transfer = new SQLDMO.Transfer2Class();

transfer.CopyAllViews = true;

transfer.CopyAllTables = false;

transfer.IncludeDependencies = false;

i tried adding the views using AddObject:

foreach (SQLDMO.DBObject o in db.ListObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj_View, SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name))

transfer.AddObject(o);

but still tables got generated!

what am i doing wrong? i know that this is doable coz i can do it using enterprise manager (it also uses DMO right?)

any help is greatly appreciated.

Thank you.

aghoneim

P.S.

Script generation options:

transfer.ScriptType = SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default |

SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops |

SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_ToFileOnly;

transfer.Script2Type = SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_UnicodeFile;

Is there a particular reason you want to use DMO? You could easily achieve what you want through SMO, which works fine against SQL 2000 You might be able to achieve what you want through DMO, but off hand I am not sure of code. If SMO won't work for you, I will try to dig up some DMO samples that "may" help you...Enterprise Manager does use DMO, but it may be doing some additional filtering on the client.

Peter

|||

Hi Peter,

I have to use DMO coz we own SQL 2000, and also coz of build server restrictions (this is for a build script).

But assuming i got perm for installing SMO on the build server, can i get SMO without buying SQL 2005?

Thanks.

aghoneim

|||Hi,

good news, yes you can.

http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hey,

Thanks alot :)

aghoneim

sql

Generating script for existing sql jobs

we have a database and we are planning to deploy the database on another Box. I have database tables,stored procedures,views,user defined functions,triggers and some jobs on this database. So instead of restoring a back of this database we are planning to create the whole environment by using the sql scrtipts. So I know how to generate the sql script for all the objects(using generate sql script at database level) but how can I get the script for my Sql Jobs. How can I copy all the sql jobs onto the the new Box.

Thanks.In Enterprise Manager, if you right click on the job and select "all tasks" you will be able to generate a script for that job.

If you have many jobs to move, you could do a restore of the msdb database on your new server since the SQL Server Agent jobs are maintained in the msdb database.

Generate scripts only for stored procedures and views?

Hello, I have two environments, testing and developing, sometimes on developing environment I make chagnes to views or stored procedures, the first time I copied the whole database, but when the tester began to test the second cycle I made the same and he losed the changes, the tables wont change, but the views and sps may change, so I need an easy way to change those objects without having to copy all the database?

I tried but the generate scrips task but I didnt see an option to script only views and procedures.

What version of SQL Server are you using? I'm using SQL Server 2005 Express, and when I go through the script wizard to script objects, I see the "Choose Object Types you want to script." just fine.

In Object Explorer, right-click on your database -> Tasks -> Generate Scripts...

|||I am using sql 2005 developer edition and I dont see that option anywhere.|||what option don't you see where and at what point?|||Did you complete the wizard? I remember that option near the end of the wizard.|||You were right, it was in the last step. I really would think to put it in the first step. Its just my opinion but thanks.

Generate scripts doesn''t take into account dependencies

I'm having a problem with the generate scripts procedure in Sql Server Express. My database has stored procedures which use views. When I generate the database create script it puts the stored procedures before the views, so that the create script fails when I try to execute it, because the stored procedure can't be compiled until the view is defined. Is there a fix for this or a way around the problem?

Jon Webb

I found a workaround / fix: 1) Make the objects I was having trouble with schema bound; 2) Check "generate dependent objects" in the generate scripts wizard.

Generate scripts doesn''t take into account dependencies

I'm having a problem with the generate scripts procedure in Sql Server Express. My database has stored procedures which use views. When I generate the database create script it puts the stored procedures before the views, so that the create script fails when I try to execute it, because the stored procedure can't be compiled until the view is defined. Is there a fix for this or a way around the problem?

Jon Webb

I found a workaround / fix: 1) Make the objects I was having trouble with schema bound; 2) Check "generate dependent objects" in the generate scripts wizard.

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 :-)