January 27, 2004 at 1:54 am
Good day!
I have several databases in one SQL Server, and having problem maintaining these - so that they are equal. Not the data, but the tables, views and SP.
Are there any way in SQL Enterprize manager or any other programs that is generating a sql script based on alter table, and alter view instead of create table and create view? I haven't found one, but I might be wrong.
I guess many of you have done this - so what's the easiest way to do it? Running a job each week, DTS?
I really don't know what to do here, so please help me. - Lars
-Lars
Please only reply to this newsgroup. All mails would be bounced back.
January 28, 2004 at 12:12 am
Lars,
I don't know of any way to script them in bulk using alter rather than create. In Query Analyzer if you right click in the object browser on Views, SPs, and UDFs and click Edit it will bring up a window with Alter rather than Create. But for tables I don't know of any that would do that.
What I would do in this situation is to use scripts to do all database modifications and then have a batch file that could be run to run osql for each script. This batch file is really easy to create if you use a database project in Visual Studio .Net. If you right click on a folder in the project it gives you the option to create a command file of all the scripts in that folder of the project. I use this all the time as I have about 15 machines I have to keep in sync. I have separate command files for views, SPs, and UDFs and I have another command file that calls each of those once for each machine. Works pretty well for me and I can even store the command files and script files in our source control application.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 28, 2004 at 5:27 am
I use EM to script all views, SPs and UDFs with only the "CREATE" option selected and then run a REPLACE ALL "CREATE " with "ALTER ".
As long as no object names end in create or alter (or you use []), this works well. If things change drastically, you occasionally get a binding error because of the order of the alter statements and have to rerun the script.
Another option that I use that works very well is the RedGate SQL Compare product that creates the scripts for the different objects with transaction rollback if something goes wrong. The only disadvantage is that you usually have to rescript (with alter as above) to ensure all dependent objects continue to work.
A cascading rescript of dependent objects would be a nice option to have!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply