Tara Kizer Blog

Tara Kizer

Generating SQL Scripts in SQL Server 2005

In SQL Server 2000, you could generate a SQL script easily using the Generate SQL Script wizard.  You could get to it from pretty much anywhere from within Enterprise Manager.  For instance, you can get to it by right clicking on any object in the database, then selecting All Tasks, and then selecting Generate SQL Script. 

In SQL Server 2005, it is now called Generate Scripts.  The only place that I can find where you can get to it is if you right click on the database, then selecting Tasks, and then selecting Generate Scripts.  The 2005 wizard does not resemble the 2000 wizard, so you have to use it a few times before you are comfortable with it. 

The most annoying thing with the new 2005 scripting wizard is that you can not generate both CREATE and DROP statements together like you could in the 2000 scripting wizard.  When you select the Script Behavior field, it indicates that this is possible.  However, it is not! Check this out:

Notice in the description that it says you can do either CREATE, DROP, or DROP followed by CREATE.  However, when you look at the dropdown options, only DROP or CREATE is available. 

I am running SQL Server 2005 service pack 1 already.  Let's hope they fix this in the next service pack.

Legacy Comments


Paul Wakeford
2006-09-20
re: Generating SQL Scripts in SQL Server 2005
I can right-click a table, sproc etc and in the popup menu I get a 'Script <object> as...' option. Still no drop then create option but a little more convenient that using the wizard each time.

Tara
2006-09-21
re: Generating SQL Scripts in SQL Server 2005
Paul,

I agree with you that that option is more convenient, however it only allows you to script one object at a time. Typically, I've got to script a bunch of objects for deployment reasons.

Kristen
2006-09-21
re: Generating SQL Scripts in SQL Server 2005
All those drop-down look harder to quickly select what you want than the old checkbox dialog boxes. Looks to be hard to see if you mis-select something in a hurry ... but there again I haven't used it, so maybe its more wonderful than it appears!

Denis the SQL Menace
2006-09-21
re: Generating SQL Scripts in SQL Server 2005
There is another way

Click the Jobs or Stored Procedures folder in SQL Server Management Studio, and then hit the F7 key; this will bring up the Summary pane. Highlight all the Jobs or Stored Procedures that you want to script using a combination of Shift and Ctrl keys, then right click, Script Job/Stored Procedure as..., and then choose where to save this script to

Chris
2006-09-21
re: Generating SQL Scripts in SQL Server 2005
After many many complaints, it looks like it definitely will be back in the next SP. Until that time i've had to make a little app which emulates the old behaviour of sql2000.

If you're looking for a start, google "scriptio" and it should point to an app on this site.

I made my own because i needed it to be consistent with 2000 for source control purposes.


2006-10-01
re: Generating SQL Scripts in SQL Server 2005
MS People are always in a hurry to release patches & that makes them make these kind of silly mistakes :)

Hefty
2006-10-10
re: Generating SQL Scripts in SQL Server 2005
I'm more curious as to why on earth this basic feature was left out.

Who are these people developing the product - obviously not from the real world :(

Edwin
2006-10-26
re: Generating SQL Scripts in SQL Server 2005
I have done Generating the SQL Server 2005 script but my scripts has no data its only the table and views structure I got.

How could I get the table in my tables from SQL Server 2005 and import it to SQL Server 2000?

Any help?


David
2006-11-02
re: Generating SQL Scripts in SQL Server 2005
I have the same problem. I can import the tables to 2000. But not like objects where the dependencies are also copied. they seem to have taken this feature out to. When you click on generate script for 2000 it doesn't work when you try to execute the script in SQL 2000. SQL 2005 sucks big time. What ever happened to DTS packages? replaced it with SISS.. doesn't help my cause.