SSMS: Script a table and its rows

SQL Server Management Studio provides the means to not only create a table to a script editor window, but to script its rows.

This tip is really handy if you’ve ever needed to copy a table from one database to another, or maybe if you’ve needed to script both a table and its rows to the DDL/Schema part of an SQLFiddle.

Here’s how:-

  1. Right click on your database in Object Explorer, and choose Tasks > Generate Scripts.Step1
  2. From the dialog, choose ‘Select specific database objects, expand the tables view and put a check in the table (or tables) you want to script.  Click Next when done.
  3. Select ‘Save to new query window’, and then click the ‘Advanced’ button in the top right hand corner.
  4. There’s a lot of options here that I might cover at another time, but for now you just need to find the ‘Types of data to script’ line (which is by default set to ‘Schema only’), and set it to ‘Schema and Data’ (this will ensure you get the DDL for the table as well as the insert statements you need to populate it.
  5. Now click OK, Next and Next again and wait for the wizard to generate your script.
  6. When done, the full script should be available in your Query Editor window.

Handy stuff, isn’t it?  Like I said, there are a load more options in the ‘Advanced’ dialog (such as choosing whether you want to add DROP table statements, extended properties, primary keys etc).  I’ll perhaps cover these and what they do in my next post, but why not have a click around and see what they do yourself?

Leave a Reply

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