home
Create object scripts using dmo from t-sql
Author Nigel Rivett
This SP will create a file containing the object create scripts.
It will script all or a single Table, Store Procedure, Function.
if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ScriptObjects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ScriptObjects]
GO
Create procedure s_ScriptObjects
@SourceDB varchar(128) ,
@SourceObject varchar(128) ,
@SourceUID varchar(128) ,
@SourcePWD varchar(128) ,
@OutFilePath varchar(128) ,
@OutFileName varchar(128) ,
@ObjectType varchar(50) -- PROCS, FUNCTIONS, TABLES
as
set nocount on
/*
exec s_ScriptObjects
@SourceDB = 'mydb' ,
@SourceObject = null ,
@SourceUID = 'sa' ,
@SourcePWD = 'password' ,
@OutFilePath = 'c:\' ,
@OutFileName = 'myfile.sql' ,
@ObjectType = 'TABLES'
*/
declare @SourceSVR varchar(128) ,
@ScriptType int ,
@FileName varchar(128) ,
@TmpFileName varchar(128) ,
@buffer varchar(8000) ,
@Collection varchar(128)
declare @context varchar(255) ,
@sql varchar(1000) ,
@rc int
select @SourceSVR = '(local)'
select @ScriptType = 4 | 1 | 64 ,
@FileName = @OutFilePath + @OutFileName ,
@tmpFileName = @OutFilePath + 'ScriptTmp.txt'
declare @objServer int ,
@objTransfer int ,
@strResult varchar(255) ,
@strCommand varchar(255)
-- get objects to script and object type
create table #Objects (name varchar(128))
if @SourceObject is not null
insert #Objects (name)
select @SourceObject
if @ObjectType = 'TABLES'
begin
if @SourceObject is null
begin
select @sql = 'insert #Objects (name) '
select @sql = @sql + 'select TABLE_NAME '
select @sql = @sql + 'from ' + @SourceDB + '.INFORMATION_SCHEMA.TABLES '
select @sql = @sql + 'where TABLE_TYPE = ''BASE TABLE'''
exec (@sql)
end
select @Collection = 'tables'
end
else if @ObjectType = 'PROCS'
begin
if @SourceObject is null
begin
select @sql = 'insert #Objects (name) '
select @sql = @sql + 'select ROUTINE_NAME '
select @sql = @sql + 'from ' + @SourceDB + '.INFORMATION_SCHEMA.ROUTINES '
select @sql = @sql + 'where ROUTINE_TYPE = ''PROCEDURE'''
exec (@sql)
end
select @Collection = 'storedprocedures'
end
else if @ObjectType = 'FUNCTIONS'
begin
if @SourceObject is null
begin
select @sql = 'insert #Objects (name) '
select @sql = @sql + 'select ROUTINE_NAME '
select @sql = @sql + 'from ' + @SourceDB + '.INFORMATION_SCHEMA.ROUTINES '
select @sql = @sql + 'where ROUTINE_TYPE = ''FUNCTION'''
exec (@sql)
end
select @Collection = 'userdefinedfunctions'
end
else
begin
select 'invalid @ObjectType'
return
end
-- create empty output file
select @sql = 'echo. > ' + @FileName
exec master..xp_cmdshell @sql
-- prepare scripting object
select @context = 'anywhere'
exec @rc = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
if @rc <> 0 or @@error <> 0 goto ErrorHnd
exec @rc = sp_OAMethod @objServer , 'Connect', NULL, @SourceSVR , @SourceUID , @SourcePWD
if @rc <> 0 or @@error <> 0 goto ErrorHnd
-- Script all the objects
select @SourceObject = ''
while exists (select * from #Objects where name > @SourceObject)
begin
select @SourceObject = min(name) from #Objects where name > @SourceObject
select @sql = 'echo print ''Create = ' + @SourceObject + ''' >> ' + @FileName
exec master..xp_cmdshell @sql
Set @sql = 'databases("' + @SourceDB + '").' + @Collection + '("' + @SourceObject + '").script'
exec @rc = sp_OAMethod @objServer, @sql , @buffer OUTPUT, @ScriptType , @tmpFileName
select @sql = 'type ' + @tmpFileName + ' >> ' + @FileName
exec master..xp_cmdshell @sql
end
-- delete tmp file
select @sql = 'del ' + @tmpFileName
exec master..xp_cmdshell @sql
-- clear up dmo
exec @rc = sp_OAMethod @objServer, 'Disconnect'
if @rc <> 0 or @@error <> 0 goto ErrorHnd
exec @rc = sp_OADestroy @objServer
if @rc <> 0 or @@error <> 0 goto ErrorHnd
-- clear up temp table
drop table #Objects
return
ErrorHnd:
select 'fail', @context
go
home