Tag Archives: SQL

SQL

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.
    Step2
  3. Select ‘Save to new query window’, and then click the ‘Advanced’ button in the top right hand corner.
    Step3
  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.
    Step4
  5. Now click OK, Next and Next again and wait for the wizard to generate your script.
    Step5
  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?

SQL

SSMS Quick Tip: Highlight errors in the SQL editor window

You’re more than likely familiar with this kind of error in the ‘Messages’ window in SSMS:-

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'from'.

Did you know that, if you double-click the error, the line of code that generated that error is highlighted in the editor window?  I only discovered this today, and already it’s something I’m using regularly.  Can’t believe I wasn’t aware of it before today.

SQL

SQL Server: Show the date a table was last modified

I’m currently developing an online application using classic asp (no negative comments please), with the data layer provided by SQL server 2008.  One of the tables I use is refreshed from another source regularly but not every day, which means that the data displayed might not always be up to date.

I wanted a simple way to display the date the data was last updated so that I could let my users know that the data they are viewing is accurate as of that date.

Turns out this can be done with a query, and the SQL is really quite simple.  Here it is:-

SELECT modify_date FROM sys.tables WHERE name = 'YOUR TABLE NAME HERE';

This is really useful, especially if you want to let your users know when the last time modifications were made to your table.

I’ve read a number of other ways to do this, but I think this is the most simple and doesn’t require any kind of admin permissions on the server.  Good stuff!