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:
- Open the Microsoft SQL Server Management Studio, and connect to the database
- Expand the Databases node, and right click on the database you want to export the schema for
- Choose “Tasks” (in 2005 “All Tasks”) then “Generate Scripts”
- Navigate through the wizard choosing your database
- 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
- Finally you will find the schema and tables to select
- 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!