|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:34 AM
Points: 141,
Visits: 61
|
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Tuesday, November 20, 2007 4:52 AM
Points: 13,
Visits: 2
|
|
Great article, makes the creation on scripts much easier. The only thing that is missing from the process is the permissions for each object they are not created, and as a result I have to create these manually. 
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Tuesday, November 20, 2007 4:52 AM
Points: 13,
Visits: 2
|
|
| Take back my comments, as View have permissions set but Tables and Stored Procedures don't
|
|
|
|
|
SSCommitted
      
Group: Moderators
Last Login: Monday, August 13, 2012 1:06 PM
Points: 1,928,
Visits: 224
|
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Thursday, January 24, 2008 1:57 AM
Points: 3,
Visits: 2
|
|
This only works for simple databases as it does not take account of dependencies between objects and can create objects in the wrong order.
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Wednesday, April 04, 2007 7:42 AM
Points: 20,
Visits: 1
|
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:34 AM
Points: 141,
Visits: 61
|
|
Thanks for the comments guys, useful feedback like this is always much appreciated. Unfortunately, yes, scripting of the table & stored proc security permissions does seem to be a limitation of this method, and was something I spotted only after submitting this article. It did make me wonder why it had not been included by MS - I'd have expected to see them applied after each object definition, or perhaps in a separate file. Other than manually scripting them I'm afraid I can't offer a reasonable way around this at present. With regard to the object order, probably the best way to deal with that is to do it with a number of passes, and not drop the objects which have already been created between each run of the script. It's messy as you'll get lots of 'object already exists' errors, but at least it does eventually solve the creation order dependency problem. How MS get around this in the upgrade scripts is something of a mystery, but it might explain why they take so long to run.
Jon
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:01 PM
Points: 215,
Visits: 609
|
|
Intersting article, and actually, I just finished one similar tool with a set of different features. I have been facing migrating database objects (several hundered sometimes)from tens of databases in different environments to some other environments. I will probably send my article to Steve sometime next weekend to share with all others.
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Wednesday, July 13, 2005 10:53 AM
Points: 98,
Visits: 1
|
|
I guess you can use DTS to create a central repository for all these scripts. Great article, Thanks!
-Isaiah
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:01 PM
Points: 215,
Visits: 609
|
|
DTS is not a good answer in terms of convenience and team cooperation. Yes, DTS has a task call "Copy SQL Server Objects" that can generate most of db objects' scripts, but I do not like it at all, I will answer it in my article in future.
|
|
|
|