Did anyone even bother to test the Generate Scripts option for a database?
I changed the following options from their default setting:
"Include Descriptive Headers" = True
"Include if NOT EXISTS" = True
"Script for Server Version" = SQL Server 2000
"Script Indexes" = True
and scripted all Stored Procedures, Tables, User-defined functions, and Views
IF NOT EXISTS (SELECT * FROM sys.objects ...
should be ...SELECT * FROM dbo.sysobjects ...
WITH (IGNORE_DUP_KEY = OFF) ...
on the indexes. That portion is incompatible with SQL 2000
EXEC sys.sp_addextendedproperty ...
should be EXEC dbo.sp_addextendedproperty ...
Is there a hotfix or patch to deal with these issues?
Additionally, the wizard only has options to generate Create statements or Drop statements. Where is the option to do both like the previous version had?
I hope patches and hotfixes are on their way soon.
Using SQL 2005 Management Studio, I need to script all selected SQL 2000 stored procedures sorted by the name of the stored procedure alphabetically. Using the script wizard, I select the stored procedures in the alphabetic listing, but it looks like the resulting script puts the stored procedures in some other rather random order. I used to be able to script in Enterprise Manager, but after installing SQL 2005 Management Studio with the SQL 2000 DTS Legacy addin, my Enterprise Manager will not work anymore. How may I create the alphabetic stored procedure script? Thank you.
I just wanted to add few more suggestions/annoyances in addition to those already mentioned regarding script generation that I have come across:
1. There could be a way to persist settings that one has chosen in the Generate Scripts dialog. IDEs are meant to boost productivity but how can this be achieved if one has to find each and every option whenever new script needs to be generated.
My suggestion: Make it possible to save script generation options. Better yet, save multiple sets of options as profiles - one to generate certain style of SQL 2000-compatible scripts, another for SQL 2005, etc. These profiles could then theoretically even be used for scripting using SMO :)
2. Is there any way to do the Generate Scripts type of thing on object level as it used to be in SQL 2000? While there are "Create To" and "Drop To" options on object level, these do not give any way to tune the scripts generation options. Its nonsence to be forced to go to the database level and start searching for the same object that I already had selected, in the wizard again!
My suggestion: Add ability to generate scripts in the same manner as the Generate Scripts task provides for single and/or multiple selected objects. The simple "Create To", "Drop To" are not enough. In my opinion SQL 2000 Enterprise Manager was much more streamlined in this sense.
I hope that fixes and additions will be available soon. Otherwise search for the tools that can create compatible scripts must begin...
Any chance the fixes for the topics in this thread will make it into SP1?
I'm interested in the DROP/CREATE being generated together (which the GUI says is an option) and I'd like to see the one stored-procedure-per-file output come back.
If you're using SS Enterprise Manager:
Right click on a database > All Tasks > Generate SQL script... (this will open the "Generate SQL scripts" box). On the Options tab you'll find an option under Table Scripting Options called Script indexes.
For SS Management Studio:
Right click on a database > Tasks > Generate Scripts... (this will open the "Script Wizard"). Select a database > click Next > In "Choose Script Options", scroll down to the bottom where you will find the option to "Script Indexes".
Unbelievable! This is a huge, huge step backwards for me from SQL Server 2000. Previously I could script my stored procedures from my development server and my production server and do a simple diff on the two files to tell which SPs were updated on the development server - it took 5 minutes. Now that's impossible because they come out in an apparently random order.
This is a bigger step backwards for me than any of the new functionality I gained from 2005.
There are other issues associated with Scripting options:
Query Analyzer allowed the following generic options that have been removed from SSMS:
Identifier Delimiter : None, Version dependent,  or ""
Do not script the collation.
Generate Transact-SQL to remove referenced component. Script tests for existence prior to attempt to remove component.
Do not include 'ON <filegroup>' clause directing filegroup use.
and table Scripting Options:
Qualify object name by its owner.
Do not include definition of identity property, seed, and increment.
Generate SET ANSI_PADDING ON and SET ANSI_PADDING OFF statements before CREATE TABLE statements.
Also Enterprise Manager Generate SQL Scripts tool had:
Files to Generate: Create one file or one file per object - Gone!
Create Drop object options - now it's one or the other.
As someone else said - why have Microsoft removed useful functionality from the scripting options. It doesn't make sense - were these options over looked or was it a conscious decision.
When will these settings be reinstated - (and they need to be able to be set for the whole IDE as well as in the wizard (as per Query Analyzer:Tools|Options )
3rd Party Scripter for me until this is fixed - damn just need to find one now!
Hi there - what i do in SQL 2000 is generate each proc to a separate file. I use different folders for my development and production servers, and then use Total Commander's option to sync folders to compare the files between the two databases. Works well for our municipal property management system.
If you have SQL 2005 you must have SP2 installed to be able to generate each object to a separate file - just installed it yesterday, trying it out right now, but i don't see why it shouldn't work. You have to turn off the "descriptive headers" option , and it runs slower, but it seems to generate cleaner scripts than 2000 - ie no extra blank lines before GO statements
Not sure about 2005, but in 2000, MS were a bit careless and add an extra blank line before the GO batch separator in the scripts, and an extra space between CREATE or ALTER, and PROCEDURE, FUNCTION, VIEW etc, in the scripts. Each time you run an ALTER script, then rescript the object, MS adds new blank lines and spaces, so idenitical objects may not have identical scripts. I made a little utility to fix this up
Seconding the request to persist the script generation settings, or being able to save option-sets / profiles, so we don't have to wade through them each time.
Used to use AutoHotKey to check the dialog box options each time for generating scripts for me in SQL2000, but its harder having those little drop down boxes just for True and False
pleease pleaase dearest microsoft don't follow your previous "for dummies" policy - give the power back to the programmers, else we'll have to get the patch called MySQL and you'll lose out to open source
I use a single hot-key which opens the Generate Script dialog, and sets all the options I most commonly use - like this....
Get the excellent open source program AutoHotKey at http://www.autohotkey.com/ Add the following script to the end of your default script file, save it, and then (after reloading the script into AutoHotKey) you can press Windows-S to generate a script of the selected database with my options. Edit the options in the script if you want to change them, or copy and past this script again with different options - and a different hot-key to have other option-sets !!!! (Works for SQL 2005 with SP2 - the list of options is not the same in each service pack)
(I'm not affiliated with AHK, just find it very useful)Code Snippet
WinWait, Microsoft SQL Server Management Studio,
IfWinNotActive, Microsoft SQL Server Management Studio, , WinActivate, Microsoft SQL Server Management Studio,
WinWaitActive, Microsoft SQL Server Management Studio,
; right-click, Tasks, Generate Script
WinWait, Script Wizard - ,
IfWinNotActive, Script Wizard - , , WinActivate, Script Wizard - ,
WinWaitActive, Script Wizard - ,
; All objects for this database
; --- OPTIONS --- t sets "true", f sets "false"
; Ansi Padding
; Append to file
; Continue scripting in error
; Convert UDDTs to base types
; Dependent objects
; Descriptive headers
; Include IF NOT EXISTS
; Include system constraint names
; Script collation
; Script CREATE
; Script Database CREATE
; Script defaults
; Script DROP
; Extended properties
; Script for Server Version (no change = SQL Server 2005)
; Script Logins
; Script object=level permissions
; Script Owner
; Script statistics (no change)
; Script USE DATABASE
; Script Vardecimal options (even Microsoft doesn't know what this is else BOL would mention it)
; --- next section of options
; Script check constraints
; Script Foreign Keys
; Script full-text indexes
; Script Indexes
; Script Primary Keys
; Script Triggers
; Script Unique Keys
; --- END OF OPTIONS
; Script to File
; ANSI text
We are currently using SQL Server 2000, and one of the features that is very useful for us is to generate separate scripts for each procedure/function.
In Enterprise Manager, we can select individual (or all) procedures/ functions, select All Tasks > Generate SQL Script..., and create scripts that will drop and reinstall procedures WITH the correct permissions:
Options... Script Object-level Permissions (checked)
File Format... Windows Text (ANSI) (selected)
Files to Generate... Create One File Per Object (selected)
This generates SEPARATE files, WITH the "drop", "create", and "grant" commands for each procedure, in the format
owner.procedurename.PRC for procedures
owner.functionname.UDF for functions
In SQL 2005, after manipulating the settings, the closest I can get is to have all of the scripts in one file, which ends with the name ".sql" and has to be manually specified. The script does contain the "create" and "grant" commands, but not the "drop" commands. This is not even close to what we need. Does anyone know of a way to do this? (From the comments above, it may STILL not be available.)