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!
