Exporting MS SQL Schema

I seem to forget this one, though I have to do it now and then, so I thought I would share this in a post. Somehow I always find myself digging into “Script Database As” instead of the much less appropriately named “Tasks” – which is where you want to be.┬áIf you want to export the schema of your MS SQL database, here is what you do:

  1. Open the Microsoft SQL Server Management Studio, and connect to the database
  2. Expand the Databases node, and right click on the database you want to export the schema for
  3. Choose “Tasks” (in 2005 “All Tasks”) then “Generate Scripts”
  4. Navigate through the wizard choosing your database
  5. You can choose “Script all objects in the selected database” on the “Select Database” page, or hit next and choose the individual objects on the following screens like Options, Schema, Tables, and Users
  6. Finally you will find the schema and tables to select
  7. On the last page of the wizard you can choose to output the schema to file, clipboard or to a new query window

Hope you find that useful!

SQL Order By using columns of Strings that may be numbers…

Had a column of type string that may be numbers that are entered by the user of an application. The user is allowed to sort, but using the standard “order by colname asc” can give this nasty list:

1
12
19
3
4
44
7
acorns
beer

Changing the command to read “order by cast(colname as signed) asc” makes the list much prettier:

1
3
4
7
12
19
44
acorns
beer

This syntax was using MySQL, but should port nicely elsewhere.