The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
The last few days we’ve spent a lot of time using SSMS features to reduce key strokes and develop T-Sql faster. Nothing will save keystrokes like leveraging pre-written code. Let’s talk about that for a couple days. We’ll hit Generating Scripts today, and then Templates and Snippets tomorrow and the next day. Return to main post.
Generating Object Scripts:
You right click any object in the object explorer and one of your options will always be Script <object> as. This might be the single biggest time saver within SSMS. Seriously. In a single click you can generate create, alter, drop statements and when working with tables complete update and insert statements. Ever written an insert statement for a 100+ column table from scratch? That gets old really fast.
Not only do these generated scripts offer select, insert, update and delete statements pre-opulated with columns and all the syntax, they even prompt for proper data types, including custom data types! In the example below I selected the Insert To option against the Person.Person table in AdventureWorks. Notice the namestyle type? Cool.
I love hot-keys. I try to keep my hands on the keyboard. But if there is ever a time mousing your way to efficiency is worth it, it’s when generating update or insert statements from an existing table.
On a side note. If you really want to avoid the mouse you can still do this with the keyboard – but it’s not all that straightforward. Mickey Stuewe and I worked it out on twitter one night. To generate an alter view for example you can mimic a right click with Shift+F10 – the rest is then just hitting keys of first letters in the dialog menu S = (Script) A=(Alter) N=(New). I think the mouse is still easier.
Generating Lots of Scripts At Once
A really quick way to generate a lot of scripts of a single object type at once, like tables, views, linked servers, SQL Server Agent jobs and the like, is to make use of the detailed object explorer.
Browse in the regular object explorer to a folder of items you’d like to script out and then hit F7. This brings up the detailed object explorer on that same folder. You can also just go to the toolbar, open the detailed object explorer and browse there, whatever.
Once you have the entire folder you can select all, or even just some of the objects and then right click and select script <object> as. Whatever you have selected gets pulled in. A great way backup specific objects such as linked servers or jobs!
Generating Complete Database Scripts
Right click on any Database in the object explorer and look under Tasks->Generate Scripts. This will bring up the Generate and Publish Scripts wizard.
Using the Generate and Publish Scripts wizard you can create a complete script of your entire schema. A collection of tables or other objects, or any combination of items within your database in a single task.
Generating scripts is a decent way to implement a rudimentary source control approach, backup your schema, or move structures from one server to another in a lightweight text based fashion. We’ll get fancier with source control, projects, solutions, and DACPACs later in the month.
Know Your Options
Wish you could script triggers or some other dependent object? No problem. All the behaviors of the generate scripts tasks are controllable within options. Back to day one. Know your options!! The image below shows a very small subset of options regarding Script Generation. There are about four times what you see below in the actual window.
Tomorrow we’ll talk about templates. You can create your own pre-written code or use hundreds of stubbed out templates already prepared for you. SSMS will even walk you through completing the parts it doesn’t know ahead of time; parameters and variables.