How to export the SQL schema in SQL Server 2005 ?


1. Manually using the GUI

Right click the database you want to export to a sql.script. Choose "Generate scripts..." from the menu that popped up.br/>

2. Scripting

In previous versions of SQL Server it was possible to export the SQL schema to a sql file/sql files using a script without installing extra software.
In SQL Server 2005 that possibility doesn't exist anymore.
 
There are various solutions to this.... :br/>

  1. Install the Database Publishing Wizard ( http://www.microsoft.com/downloads/en/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en )
  2. Install the "Data transformation services"
  3. SQL-dmo scripting

 

2.1. Exporting the SQL schema using the Database Publishing Wizard

keep in mind that this method does not export triggers and indexes.

sqlpubwiz script ( -C connection_string | -d local_database_name ) (output_file) [switches]

switches:

 Output options:

  [-f] :                       Overwrite existing files
  [-noschemaqualify] :         Output script does not qualify object names with
                             schema name.
  [-schemaonly] :              Only script schema
  [-dataonly] :                Only script data
  [-targetserver] :            Specifies what version of SQL Server the script
                             should target.  Valid versions are "2000", "2005".
                             Default Value: 2005
  [-q] :                       Suppress output
  [-nodropexisting] :           Default Value: False

 Local connection related:

  [-C connection_string] :     Connection string
  [-d local_database_name] :   Database name to script
  [-U local_user_name] :       Username
  [-P local_password] :        Password
  [-S local_server_name] :     Server name

 
 br />