none
Generate Scripts option in SQL 2005 Management Studio

    Question

  • 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

    Problems:
    1.  Generates
    IF NOT EXISTS (SELECT * FROM sys.objects ...
    should be ...SELECT * FROM dbo.sysobjects ...

    2. Generates
    WITH (IGNORE_DUP_KEY = OFF) ...
    on the indexes. That portion is incompatible with SQL 2000

    3.Generates
    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.

    Thanx,


    Tuesday, December 13, 2005 5:47 AM

Answers

  • Robert

    Microsoft is aware of this problem and we are working on a fix.

    Thanks

    Grigory

    Wednesday, December 21, 2005 7:51 PM

All replies

  • Robert

    Microsoft is aware of this problem and we are working on a fix.

    Thanks

    Grigory

    Wednesday, December 21, 2005 7:51 PM
  • 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.
    Monday, January 16, 2006 6:44 PM
  • 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...

    Thanks

    Tuesday, January 17, 2006 12:15 PM
  • Hi,

    Is there a patch for this problem yet?
    Friday, January 27, 2006 4:16 AM
  • Any update on this?

    Also, the ability to drop and create in one step is missing even though the option descirption implies it should be avialable.

    Can you provide a time frame for a solution?

     

    Glenn

    Wednesday, March 29, 2006 4:44 PM
  • I noticed the same problems... Just take note that users will start to wonder... Visual Studio seems to get it right.
    Thursday, April 06, 2006 7:54 PM
  • Grigoriy,

    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.

    Thanks.

     

    Wednesday, April 12, 2006 10:22 PM
  • I'm not sure if I'm missing anything in the wizard, but is there an option to script all object to a seperate file as in 2000? That was phenomally useful.

    Thursday, April 13, 2006 9:54 AM
  • Where did you set these scripting options?  I am trying to script a large number of tables along with their non-clustered indexes.  All the CREATE script gives me is the clustered index.
    Tuesday, May 02, 2006 3:17 PM
  • Hi Mark

    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".

    Max

    Tuesday, May 02, 2006 3:36 PM
  • Thank you.  I didn't even know there was a Generate Scripts wizard, probably because there is nothing about it in the BOL.
    Tuesday, May 02, 2006 9:22 PM
  • I just stumbled across this post. Thanks Max for the description of how to get at the Script Wizard.

    I would also like to add my voice to the requests that the backwards compatibility issues with scripting to SQL Server 2000 be patched soon.

    Wednesday, May 03, 2006 10:21 AM
  • Has this stuff been fixed yet?  why market it as a feature when you know it doesn't work.  Now I'm screwed
    Friday, June 09, 2006 12:06 AM
  • I'm just curious as to how this ever made it out of QA?  I work in the dev dept of a mortgage wholesaler, building home grown .NET solutions.  The kind of issues I've seen here and in other posts wouldn't make it past the first iteration. WTF?
    Wednesday, June 28, 2006 5:48 PM
  • Grigoriy posted earlier that Microsoft is working on this problem. Any sign of a fix in the near future Grigory? Anyone at Microsoft?
    Tuesday, July 04, 2006 4:06 PM
  • Is there a fix for this problem yet?

    It is a pain to manually convert scripts to make them compatible with SQL Server 2000!
    Thursday, August 24, 2006 1:01 AM
  • 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.
    Tuesday, September 05, 2006 8:55 PM
  • SQL Delta ($225) is a great tool for comparing databases, and is not a huge overwhelming thing.  www.sqldelta.com.  I'm not affiliated with them, I just like it.

     

    David Walker

    Thursday, September 14, 2006 2:54 PM
  • 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!

    Alex








    Friday, September 22, 2006 1:18 PM
  • Any word yet?

    I need the ability to script objects to separate files so that I can easily save them to SourceSafe.


    Wednesday, October 25, 2006 8:35 PM
  • 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
    Sunday, September 16, 2007 8:31 AM
  • 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 Smile and you'll lose out to open source

    Sunday, September 16, 2007 8:52 AM
  • 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

    #s::

    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
    Send {APPSKEY}te
    WinWait, Script Wizard - ,
    IfWinNotActive, Script Wizard - , , WinActivate, Script Wizard - ,
    WinWaitActive, Script Wizard - ,
    ; All objects for this database
    Send {ALTDOWN}a{ALTUP}
    ; Next
    Send n

    ; --- OPTIONS --- t sets "true", f sets "false"
    ; Ansi Padding
    Send t{ENTER}{DOWN}
    ; Append to file
    Send f{ENTER}{DOWN}
    ; Continue scripting in error
    Send t{ENTER}{DOWN}
    ; Convert UDDTs to base types
    Send f{ENTER}{DOWN}
    ; Dependent objects
    Send t{ENTER}{DOWN}
    ; Descriptive headers
    Send f{ENTER}{DOWN}
    ; Include IF NOT EXISTS
    Send f{ENTER}{DOWN}
    ; Include system constraint names
    Send t{ENTER}{DOWN}
    ; Script collation
    Send t{ENTER}{DOWN}
    ; Script CREATE
    Send t{ENTER}{DOWN}
    ; Script Database CREATE
    Send t{ENTER}{DOWN}
    ; Script defaults
    Send t{ENTER}{DOWN}
    ; Script DROP
    Send f{ENTER}{DOWN}
    ; Extended properties
    Send t{ENTER}{DOWN}
    ; Script for Server Version (no change = SQL Server 2005)
    Send {DOWN}
    ; Script Logins
    Send t{ENTER}{DOWN}
    ; Script object=level permissions
    Send t{ENTER}{DOWN}
    ; Script Owner
    Send t{ENTER}{DOWN}
    ; Script statistics (no change)
    Send {DOWN}
    ; Script USE DATABASE
    Send t{ENTER}{DOWN}
    ; Script Vardecimal options (even Microsoft doesn't know what this is else BOL would mention it)
    Send t{ENTER}{DOWN}

    ; --- next section of options
    Send {DOWN}
    ; Script check constraints
    Send t{ENTER}{DOWN}
    ; Script Foreign Keys
    Send t{ENTER}{DOWN}
    ; Script full-text indexes
    Send t{ENTER}{DOWN}
    ; Script Indexes
    Send t{ENTER}{DOWN}
    ; Script Primary Keys
    Send t{ENTER}{DOWN}
    ; Script Triggers
    Send t{ENTER}{DOWN}
    ; Script Unique Keys
    Send t{ENTER}

    ; --- END OF OPTIONS
    ; Next
    Send {ALTDOWN}n{ALTUP}
    ; Script to File
    Send {ALTDOWN}l{ALTUP}
    ; ANSI text
    Send {ALTDOWN}a{ALTUP}
    Return
    Monday, September 17, 2007 11:16 AM
  • I have SS2k8 and it still adds blank lines after user defined functions, everything else is fine.  Anyone ever found a fix for this?  Am I missing a new setting?
    Wednesday, June 17, 2009 7:33 PM
  • 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.)

    Thanks,
    Paul

    Wednesday, July 01, 2009 3:38 PM