Tip SSMS: Script your Entire Table including the Data – a hidden gem.


Have you ever written a TSQL SELECT statement that builds INSERT Statements so you can script the entire table? Usually we do it to make it simple to create & load a “Reference or Lookup” table? Sure, Most DBA’s have.
This Waay Cool little feature is buried in the options. You’ll never need to write your own again.

The Basics:

Most people have found you can script out a single table or stored procedure. That capability was there in SQL 2000. Just Right-Click a Table, View, Stored Proc or other object.

image

The Handy:

But click a little higher, at the Database Node & you discover this little gem. Generate Scripts…

image

This opens the “Generate SQL Server Scripts Wizard” which might not seem that exciting. Until you get to the 3rd pane & discover this little sleeper. Script Data = True
It is set to False by default. So it will only reward the inquisitve.

image

And faster than you can say “U Beauty”, instant reference table, just run script.

image

I’m not saying that SSIS is redundant, Bulk-Copy is highly valuable. But for those little reference tables, this is a winner.

In case you are wondering.
Yes the Generate Scripts Wizard is included in SQL 2005.
No the Script Data option is not in SQL 2005.

So I’d recommend you upgrade (there is heaps of other value you are missing out on, things that save big $$$). But if you can’t, a work around is to install the SQL 2008 tools on one box & this should then work for you. I’ve not checked if this is possible from the SQL 2008 Express Edition. I suspect you may need SQL Server 2008 Developer Edition. 

Enjoy
      Dave.

Crap thought for the Post: Is it possible to catch both Swine Flu & Averian(Bird) Flu while playing a game of Reversi? And if you did, would you dream of Pigs flying backward?

Comments (4)

  1. Mike "Postal Mike" Durthaler says:

    Dave,

    SQL Server nested this option in 2008 R2.  Under the "General" rather than the "Table" options and as if to hide it to get even with you for exposing it, is the last option in the list "Types of Data to Script".  And in the drop down is "Schema and data."

    Thus my cardinal rule: "If an option is convenient and logically categorized and you're customized to it, it will be nested and/or renamed and recategorized in an illogical manner in an upcoming upgrade."  The wording is a work in progress but it's happened to me far too often with Visual Studio and SSMS.

    The post is VERY handy and got me inquisitive.  I like your wording in your post and I was indeed rewarded.

    Thanks!

  2. Truck says:

    This does work in the 2012 Express Edition

  3. Tim says:

    SQL Server 2014 Management Studio (v 12.0) look out for "Types of data to script" under the General tab.

  4. faith says:

    How can i connect mxopc data tag in sql server management studio? My goal is to logs the data.