CREATE procedure dmoScriptDatabaseCollection
@dmoServer int,
@serverName varchar(255),
@databaseName varchar(255),
@collectionName varchar(255),
@path varchar(255),
@style int = 0,
@debug bit = 0
as
/*
Use this procedure to generate scripts useable to recreate the objects within
a database object or the JobServer object
STYLE - 0 means to pu all objects in one file, 1 means to put each object in its
own file
*/
declare @dmoMethod varchar(255)
declare @dmoProperty varchar(255)
declare @file varchar(255)
declare @hr int
declare @boolSystemObject tinyint
declare @objectCount int
declare @curObjectNb int
declare @objectName varchar(256)
declare @osCmd varchar(255)
declare @msg varchar(255)
set nocount on
If @collectionName not in ("DatabaseRoles","Defaults","FileGroups","FullTextCatalogues","Rules",
"StoredProcedures","SystemDatatypes","Tables","TransactionLog","UserDefinedDatatypes","Users","Views")
begin
select @msg = "The " + @collectionName + " collection is not available from the
database object. (admin..dmoScriptDatabaseCollection)"
raiserror (@msg, 16, 1)
return
end
/* clean up existing files */
if @style = 0
begin
select @file = @path + @serverName + "_Database_" + @databaseName + "_" + @collectionName + ".sql"
select @osCmd = "del " + @file
exec master..xp_cmdshell @osCmd, no_output
end
/* get the number of elements in the collection */
select @dmoProperty = 'Databases("' + @databaseName + '").' + @collectionName + '.Count'
exec @hr = master..sp_OAGetProperty @dmoServer,@dmoProperty,@objectCount OUT
if @hr <> 0
begin
exec sp_displayoaerrorinfo @dmoServer, @hr
return
end
select @curObjectNb = 1
while @curObjectNb <= @objectCount
begin
if @collectionName in ("Tables", "StoredProcedures","Views","Triggers","Users")
begin
/* only script user objects */
select @boolSystemObject = 0
select @dmoProperty = 'Databases("' + @databaseName + '").' + @collectionName
+ '.Item(' + convert(varchar(5),@curObjectNb) + ').SystemObject'
exec @hr = master..sp_OAGetProperty @dmoServer,@dmoProperty,@boolSystemObject OUT
if @hr <> 0
begin
exec sp_displayoaerrorinfo @dmoServer, @hr
return
end
end
if @boolSystemObject = 0
begin
/* get the object name */
select @dmoProperty = 'Databases("' + @databaseName + '").' + @collectionName
+ '.Item(' + convert(varchar(5),@curObjectNb) + ').Name'
exec @hr = master..sp_OAGetProperty @dmoServer,@dmoProperty,@objectName OUT
if @hr <> 0
begin
exec sp_displayoaerrorinfo @dmoServer, @hr
return
end
select @dmoMethod = 'Databases("' + @databaseName + '").' + @collectionName + '("' + @objectName + '").Script'
if @style = 0
begin
/* put all object scripts in a single file */
exec @hr = master..sp_OAMethod @dmoServer,@dmoMethod,NULL,295,@file
end
else
begin
/* put each object script in its own file */
select @file = @path + @serverName + "_Database_" + @databaseName + "_" +
substring(@collectionName,1,DATALENGTH(@collectionName)-1) + "_" + @objectName + ".sql"
exec @hr = master..sp_OAMethod @dmoServer,@dmoMethod,NULL,39,@file
end
if @hr <> 0
begin
exec sp_displayoaerrorinfo @dmoServer, @hr
return
end
end
select @curObjectNb = @curObjectNb + 1
end