 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Generate 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)? >I found a suggestion on Connect for this: > [quoted text clipped - 6 lines] > > Ben > 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 > 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. 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 > > 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). [quoted text clipped - 9 lines] > 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. > 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, >> You can always write a little script in any language that supports >> regular [quoted text clipped - 11 lines] > 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 > You can even use the following syntax: > [quoted text clipped - 5 lines] > > which should allow you to search and replace in one pass That's really slick Jesse ... thanks! >I found a suggestion on Connect for this: > [quoted text clipped - 4 lines] > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.