One of the things I seem to find myself doing (a LOT) is scripting out objects in SQL Server. This might mean creating a SQL script to CREATE, INSERT or UPDATE a table. Let’s take a look at some ways SQL Management Studio can help with this task.
When we are working with a table that we want to create a SQL script for, we can simply right click on that table, go to Script Table As, and then we get a lot of choices (CREATE, DROP, DROP and CREATE …). These choices are all pretty straight forward.
Still … there is a trick here, and I don’t see a lot of people using it. Maybe it’s just me, maybe I’m lazier than the average dev, but I often find myself using the Script Table As menu and choosing SELECT To and Clipboard. This creates a nice select statement with all my fields wrapped in hard brackets. I can then copy this into an INSERT query I might be working on to save myself some typing. I can quickly copy the field list from the Query ‘Script Table As’ gives me and use it in the top of my INSERT query, then I can copy the entire SELECT query into the bottom of my INSERT query and Bob’s yer Uncle, I’ve got a simple INSERT query ready to go. Note: This is most useful when I’m trying to create a new table based on an existing table with only minor changes to field names. I use this frequently when I’m establishing a reporting database based on staging tables.
Script Table is a pretty handy menu, and looking at the screenshot above you’ll see it gives you lots of options. The one thing it won’t give you is the ability to script multiple tables at once. To do that, we’ll get to the same menu from a different place. Press F7 on your keyboard while in SQL Management Studio and you’ll open a new tab showing Object Explorer Details. By default, the Object Explorer Details will show the objects in the database you are currently working in. If you want to explore a different database you can use the ‘Up Folder’ button on the toolbar in the top of the tab to travel up through the hierarchy until all your databases are displayed.
Once you are in the database you want, you can click into the tables collection, and then you can CLICK-SHIFT CLICK (Click the first table you want and SHIFT CLICK the last table you want to select all tables between) or you can CTRL-CLICK each table you want and you will now be able to right click and choose ‘Script Table As’ to make CREATE, DELETE or DELETE and CREATE scripts for all the tables you selected.
The third way to do this is the most powerful. At the database level in Object Explorer, right click and choose Tasks – Generate Scripts
This will open a wizard. The first screen gives you a little general guidance. Click Next to get to the cool stuff.
from the next screen, you can script almost everything in the database. You can select ALL objects or any combination you like.
That’s cool, but the real power is in the next screen.
Click Advanced and you will be presented with the Advanced Scripting Options dialog. In this screen, you can choose exactly what will be in your scripts. You can choose a version of SQL Server to script to (so your script will be compatible with the version of SQL Server it will run on ), you can choose to CREATE, DROP and CREATE, include a USE DATABASE in your script. SO MANY OPTIONS! One of the coolest options here is we can script the DDL (the data definition language, or the CREATE, UPDATE or DELETE script itself) AND the data within the table. With this method, we can create scripts that both CREATE and POPULATE the table. Very useful.
So … That’s at three ways you can script a table from SQL Server. I’m sure there are others (including writing the script yourself of course). Did I miss anything? Let’s hear about it in the comments 🙂