Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 24, 1999

Automated Database Schema Scripting (DMO)

By Bill Wunder

Having scripts to recreate your SQL Server environment is a crucial part of a good backup/recovery plan. It is possible to use the GUI to "Generate SQL Scripts...". This can be time consuming and prone to the drudgery and errors of manual procedures. It is not possible to schedule script creation using the GUI. SQL_DMO is a useful to automate the process and assure the quality and consistency of the scripts produced.

Here are a set of stored procedures that will script the objects in a database for use in recreating or documenting that database.

There are two driver procedures. One to script collections of the server object (dmoScriptServer) and another to script collections of the database object (dmoScriptDatabase). Select either driver name for a walk through of the code for that process.

The server objects that support the script method are "Jobserver.Jobs", "Logins" and "Database". I encountered numerous problems with the "Database" object's script method and have decided to use an alternate method to produce the CREATE DATABASE scripts. Also, I will still need to build a script that sets the passwords for the logins to the correct values in the event I have to use the script(s) produced from the Logins object. This password script will have to be built manually and kept in a secure location.

The database object is much more effective in producing good scripts. The collections in the database include tables, stored procedures, views, databaseroles, and views. There are several collections within each table object (indexes, constraints, triggers, DRI, ...). One unfortunate issue that was reported is that objects that don't belong to dbo are not scripted. I don't have this problem because I don't have any objects that aren't owned by dbo.

Instructions for installing the scripts.

Load these scripts onto a SQL7 Standard or Enterprise. They won't work on Desktop Edition SQL Server because trusted security is used throughout. (You can modify to use SQL Server Security by changing the dmoConnect and modifying the single osql call, located in dmoScriptDatabases, to include a login name and password

These scripts were all produced with the utility. I decided not to put them in a zip file to FORCE you to have to look at the code.

A database to hold the process:
admin
Two tables needed for the CREATE DATABASE workaround:
databaseFileInfo
databaseFileGroupInfo
A handful of stored procedures:
script_user_xp
dmoConnectServer
dmoScriptServer
dmoScriptServerCollection
dmoScriptDatabase
dmoScriptDatabaseCollection
dmoScriptTables
dmoScriptTableCollection
sp_displayoaerrorinfo (from BOL - mostly)
sp_hexadecimal (from BOL)
ScriptCreateDatabase
dmoDisconnectServer

Create the database - use the script or just simply create a database with the defaults using the wizard. I will be posting additional scripts for inclusion into this database as time permits.

Create the tables - The tables should never have more than a dozen rows and are truncated before each use, so indexes are not useful.

Create the Stored Procedures.

Determine the path to where you want the scripts to be stored. Create and non existing folders to establish the path.

This how you call the procedures. Replace the word in the brackets and the brackets with valid values from your environment. If you want to script logins make a call to

exec admin..dmoScriptServer [server name], [output path]
If you want to script a database make a call to
exec admin..dmoScriptDatabase [server name], [database name], [output path]
If you want to script each object to a separate file the command would look like:
exec admin..dmoScriptServer [server name], [output path], 1
or
exec admin..dmoScriptDatabase [server name], [database name], [output path] , 1

Let me know if you have any problems , questions, or corrections.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM