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) - Page 9

By Bill Wunder


CREATE procedure dmoScriptDatabase
  @serverName varchar(30),
  @databaseName varchar(30),
  @outputPath varchar(255) = 'c:\',
  @style tinyint = 0
as
declare @objectName varchar(255)
declare @dmoServer int
declare @cmd varchar(255)

/* uses a trusted osql connetion back to the admin database to create an output file
    containing the CREATE DATABASE statement for the specified database */
select @cmd = 'osql -U sa -E -S ' + @@SERVERNAME + ' -Q "admin.dbo.ScriptCreateDatabase '+ 
                        @serverName +', ' + @databaseName + '" -o '+ @outputPath + @serverName + '_Databases_' + @databaseName +'.sql -h-1 -w2000' 
exec master..xp_cmdshell @cmd 

/* use Integrated Security for the OLE process */
exec dmoConnectServer @serverName, @dmoServer OUT

if @dmoServer > 0
  begin
    /* each table object has several collections to process */
    exec dmoScriptTables @dmoServer, @serverName, @databaseName, @outputPath, @style

    /* other database objects can use a generic script generator */
    exec dmoScriptDatabaseCollection @dmoServer, @serverName,  @databaseName, 'Users', @outputPath, @style
    exec dmoScriptDatabaseCollection @dmoServer, @serverName,  @databaseName, 'DatabaseRoles', @outputPath, @style
    exec dmoScriptDatabaseCollection @dmoServer, @serverName, @databaseName, 'StoredProcedures', @outputPath, @style
    exec dmoScriptDatabaseCollection @dmoServer, @serverName, @databaseName, 'Views', @outputPath, @style
    exec dmoScriptDatabaseCollection @dmoServer, @serverName, @databaseName, 'Rules', @outputPath, @style 
    exec dmoScriptDatabaseCollection @dmoServer, @serverName,  @databaseName, 'Defaults', @outputPath, @style
    exec dmoScriptDatabaseCollection @dmoServer, @serverName, @databaseName, 'UserDefinedDatatypes', @outputPath, @style 

    /* a little clean up */
    exec dmoDisconnectServer @dmoServer
  end




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


















Thanks for your registration, follow us on our social networks to keep up-to-date