How to script all my database objects? Here is the answer.

Blog do Ezequiel
Portuguese PFE SQL Server Team


Latest Updates
10/05/2014 - Latest update on the AdaptiveIndexDefrag procedure v1.5.8. Look for it in the "Ezequiel shortcuts" section on the right or just click HERE.

10/05/2014 - Updates to Maintenance plan deployment scripts available HERE

04/06/2014 - The SQL Swiss Army Knife Series Index

How to script all my database objects? Here is the answer.

Rate This
  • Comments 2

One question that sometimes arises is "How can we script all database objects throught some kind of automation?"


You have several ways to do it.
For both SQL 2000 and SQL 2005 you have Microsoft SQL Server Database Publishing Wizard that generates a single SQL script file which can be used to recreate a database (both schema and data).

Other options are available in Codeplex (Open Source Community) for schema purposes:

 1) ScriptDB
   It's a console application, that works for both SQL 2000 and SQL 2005.
   Creates one script file for each object. You can choose to script objects with bcp.exe and you also have the option of scriting extended properties if you want to.
   
 2) ExportSQLScript
   This is a command line tool which exports both SQL Server 2000 and 2005 objects in a single or multiple scripts files in order to create a database.
   It was also designed for revision control purposes.

 3) Database Synchronizer
   This command line tool works for both SQL 2000 and SQL 2005 and has three modes of operation:   

                - Generate scripts for an existing database
                - Synchronize between two pre-existing databases
                - Take a set of scripts and update the schema of a target database to make it equivalent to those scripts. 

You can also do your own script using SQL-DMO or SQL-SMO to response to your own specific needs.

Finally you can read the following Microsoft Support article: How to generate a script of the necessary database metadata to create a statistics-only database in SQL Server 2005

Check it out!

Social Media Sharing
|
Leave a Comment
  • Please add 4 and 5 and type the answer here:
  • Post
Page 1 of 1 (2 items)