A Better Way To Script Database Objects

Photo by Andy Beales on Unsplash

Happy New Year! My New Year’s resolution for 2018 is to help you become a better SQL developer.

I want to start off with that today by showing you a much better way to generate database object change scripts.

Prefer video?  Watch this week’s post on YouTube instead!

If you are like I used to be for YEARS, anytime you want to copy a table, index, etc… you probably right click on that object in SQL Server Management Studio and click “Script <Table|Index|View|etc…> as” > “CREATE To”:

An inefficient way of generating change scripts

This is a pretty easy way to quickly script database objects, however it’s incomplete.

For starters, I’m forced to generate the scripts for tables one at a time.  Not fun.

Additionally, if I want to script associated objects for that table, like indexes, I have to go to each index and then right-click and select “Script Index as”.  Ugh.

There Is A Better Way

Instead of using “Script Table as”, you can right click on your database and choose “Tasks” > “Generate Scripts…”:

This option brings up a GUI that will allow you to script multiple objects at the same time:

Look ma, multiple objects at once!

Additionally if you click the “Advanced” button in the final page of the dialog, you will receive many more options for how your objects will get scripted, including the ability to script out the indexes!

Ooooooo, ahhhh – everything scripted in one fell swoop!

Using the Generate Scripts Task it’s easy to generate table, index, stored procedure, etc… change scripts all in one step – nice!

NOTE: If your SQL User receives an error when trying to generate the scripts, make sure they have the following access:

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

8 thoughts on “A Better Way To Script Database Objects”

  1. Hi Gerald! No I haven’t. I could see that working well if I had to do it programmatically.

    The lazy me likes a GUI for adhoc tasks, but a query version (that would also grab constraints, indexes) could be useful in certain situations.

  2. The first method, Script Table As… Create…. will also script indexes; you don’t have to do that in a separate step. You just need to set the option to have indexes scripted. The same is true for other things like data compression settings.

    1. Great tip, thanks! I never knew of that option before. Definitely makes scripting single tables easier.

      If anyone is curious, the option can be turned on in Tools > Options > Scripting > Script Indexes = True

  3. The scripting functionality of the Object Browser is provided by the SMO library. It can be worthwhile to learn how to do this in your own programs for special cases.

    The best example I’ve coded is a PowerShell program to take the output from Upgrade Advisor with all the descriptions of what rule is violated. It makes a list of all the objects with warnings, and uses SMO to script each one to a separate file. The warnings raised by Upgrade Advisor for that object are included as a block comment at the beginning of the file. The output files are distributed to separate folders in a source control system with the structure of your choosing.

  4. What I find frustrating about this is that, say, you set up a gazillion options to get it “just so” you won’t be able to save the option set. If you need to do the same thing again at another time or on another server you have to fiddly faff reset options and undoubtedly don’t get them exactly the same. It would be great to be able “meta-script” this sort of thing.

    Maybe the powershell script option as described is the way to go, though it’s not always allowed on client’s servers. SMO is an interesting alternative too.

Leave a Reply

Your email address will not be published. Required fields are marked *