September 28, 2010 at 5:12 am
Hi There,
Does anyone know how to disable the IF EXISTS....DROP part when using sp_OA.... stored procs to output objects to a txt file?
I have a script which uses the sp_OA.... stored procs to output all objects structure of a database to a txt document. Though when I open the txt file, I see it also included the IF EXIST.....DROP query ( which I dont want ).
The script is as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_ScriptAllDatabases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ScriptAllDatabases]
GO
CREATE procedure s_ScriptAllDatabases
@SourceUIDvarchar(128) ,-- null for trusted connection
@SourcePWDvarchar(128) ,
@OutFilePathvarchar(256) ,-- Root path - will add directory for object types
@OutFileNamevarchar(128) , -- null for separate file per object script
@WorkPathvarchar(256) ,
@SourceSVRvarchar(128) = null ,-- to script remote server
@Databasevarchar(128) = null-- to script single database / jobs / DTS
as
/*
execs_ScriptAllDatabases
@SourceUID= null ,
@SourcePWD= null ,
@OutFilePath= 'C:\ServerScripts\' ,
@OutFileName= null ,
@WorkPath= 'C:\ServerScripts\' ,-- no spaces
@SourceSVR= null
execs_ScriptAllDatabases
@SourceUID= null ,
@SourcePWD= null ,
@OutFilePath= 'c:\a\' ,
@OutFileName= null ,
@WorkPath= 'c:\temp\' ,-- no spaces
@SourceSVR= null ,
@Database= 'JOBS'
execs_ScriptAllDatabases
@SourceUID= null ,
@SourcePWD= null ,
@OutFilePath= 'c:\a\' ,
@OutFileName= null ,
@WorkPath= 'c:\temp\' ,-- no spaces
@SourceSVR= null ,
@Database= 'DTS'
execs_ScriptAllDatabases
@SourceUID= null ,
@SourcePWD= null ,
@OutFilePath= 'c:\a\' ,
@OutFileName= null ,
@WorkPath= 'c:\temp\' ,-- no spaces
@SourceSVR= null ,
@Database= 'mydb'
*/
declare @sql varchar(1000) ,
@cmdvarchar(1000)
if @SourceSVR is null
begin
select @SourceSVR = @@servername
end
if right(@OutFilePath,1) <> '\'
begin
select @OutFilePath = @OutFilePath + '\'
end
if right(@WorkPath,1) <> '\'
begin
select @WorkPath = @WorkPath + '\'
end
select@OutFilePath = @OutFilePath + '"' + @SourceSVR + '"'
exec master..xp_cmdshell @cmd
select@OutFilePath = @OutFilePath + '\'
select @sql =
'selectname
from[' + @SourceSVR + '].master.dbo.sysdatabases
wherename <> ''tempdb'''
if @Database is not null
begin
select @sql = @sql + ' and name = ''' + @Database + ''''
end
create table #tblDatabases (name varchar(128))
insert#tblDatabases
(name)
exec (@sql)
declare@FilePathvarchar(256)
declare@namevarchar(128) ,
@maxnamevarchar(128)
select@name = '' ,
@maxname = max(name)
from#tblDatabases
while @name < @maxname
begin
select@name = min(name) from #tblDatabases where name > @name
select@FilePath = @OutFilePath + '"' + @name + '"'
-- output current database name
select CurrentDatabase = @name
-- create output directory - will fail if already exists but ...
select@cmd = 'mkdir ' + @FilePath
exec master..xp_cmdshell @cmd, no_output
execs_ScriptAllObjectsInDatabase
@SourceDB = @name ,
@SourceUID = @SourceUID ,
@SourcePWD = @SourcePWD ,
@OutFilePath = @FilePath ,
@OutFileName = @OutFileName ,-- null for separate file per object script
@WorkPath= @WorkPath ,
@SourceSVR= @SourceSVR
end
if coalesce(@Database, 'JOBS') = 'JOBS'
begin
select@FilePath = @OutFilePath + 'JOBS'
-- create output directory - will fail if already exists but ...
select@cmd = 'mkdir ' + @FilePath
exec master..xp_cmdshell @cmd, no_output
execs_ScriptObjects
@SourceDB= 'msdb' ,
@SourceObject= null ,-- null for all objects
@SourceUID = @SourceUID ,
@SourcePWD = @SourcePWD ,
@OutFilePath = @FilePath ,
@OutFileName = @OutFileName ,-- null for separate file per object script
@ObjectType= 'JOBS' ,
@WorkPath= @WorkPath ,
@SourceSVR= @SourceSVR
end
if coalesce(@Database, 'DTS') = 'DTS'
begin
select@FilePath = @OutFilePath + 'DTS'
-- create output directory - will fail if already exists but ...
select@cmd = 'mkdir ' + @FilePath
exec master..xp_cmdshell @cmd, no_output
execs_ScriptObjects
@SourceDB= 'msdb' ,
@SourceObject= null ,-- null for all objects
@SourceUID = @SourceUID ,
@SourcePWD = @SourcePWD ,
@OutFilePath = @FilePath ,
@OutFileName = @OutFileName ,-- null for separate file per object script
@ObjectType= 'DTS' ,
@WorkPath= @WorkPath ,
@SourceSVR= @SourceSVR
end
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_ScriptAllObjectsInDatabase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ScriptAllObjectsInDatabase]
GO
Create procedure s_ScriptAllObjectsInDatabase
@SourceDBvarchar(128) ,
@SourceUIDvarchar(128) ,-- null for trusted connection
@SourcePWDvarchar(128) ,
@OutFilePathvarchar(256) ,-- Root path - will add directory for object types
@OutFileNamevarchar(128) , -- null for separate file per object script
@WorkPathvarchar(256) ,
@SourceSVRvarchar(128)
as
if right(@OutFilePath,1) <> '\'
begin
select @OutFilePath = @OutFilePath + '\'
end
if right(@WorkPath,1) <> '\'
begin
select @WorkPath = @WorkPath + '\'
end
set nocount on
declare @tblObjectType table (ObjectType varchar(50))
insert@tblObjectType select 'PROCEDURES'
insert@tblObjectType select 'FUNCTIONS'
insert@tblObjectType select 'TABLES'
insert@tblObjectType select 'VIEWS'
insert@tblObjectType select 'INDEXES'
insert@tblObjectType select 'TRIGGERS'
insert@tblObjectType select 'DEFAULTS'
insert@tblObjectType select 'RULES'
declare@FilePathvarchar(256) ,
@cmdvarchar(1000)
declare@ObjectTypevarchar(50) ,
@maxObjectTypevarchar(50)
select@ObjectType = '' ,
@maxObjectType = max(ObjectType)
from@tblObjectType
while @ObjectType < @maxObjectType
begin
select@ObjectType = min(ObjectType) from @tblObjectType where ObjectType > @ObjectType
select@FilePath = @OutFilePath + @ObjectType
-- create output directory - will fail if already exists but ...
select@cmd = 'mkdir ' + @FilePath
exec master..xp_cmdshell @cmd, no_output
execs_ScriptObjects
@SourceDB = @SourceDB ,
@SourceObject = null ,
@SourceUID = @SourceUID ,
@SourcePWD = @SourcePWD ,
@OutFilePath = @FilePath ,
@OutFileName = null ,-- null for separate file per object script
@ObjectType = @ObjectType ,
@WorkPath= @WorkPath ,
@SourceSVR= @SourceSVR
end
GO
if exists (select * from dbo.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
@SourceDBvarchar(128) ,
@SourceObjectvarchar(128) ,-- null for all objects
@SourceUIDvarchar(128) ,-- null for trusted connection
@SourcePWDvarchar(128) ,
@OutFilePathvarchar(256) ,
@OutFileNamevarchar(128) , -- null for separate file per object script
@ObjectTypevarchar(50) ,-- PROCS, FUNCTIONS, TABLES, VIEWS, INDEXES
@WorkPathvarchar(256) ,
@SourceSVRvarchar(128)
as
/*
exec s_ScriptObjects
@SourceDB= 'allan',
@SourceObject= null ,-- null for all objects
@SourceUID= null ,-- null for trusted connection
@SourcePWD= null ,
@OutFilePath= 'C:\ServerScripts\' ,
@OutFileName= null , -- null for separate file per object script
@ObjectType= 'VIEWS' ,-- PROCS, FUNCTIONS, TABLES, VIEWS, INDEXES
@WorkPath= 'C:\ServerScripts\' ,
@SourceSVR= 'ITW2XP2WG1_N'
*/
set nocount on
declare@ScriptTypeint ,
@FileNamevarchar(256) ,
@tmpFileNamevarchar(256) ,
@buffervarchar(8000) ,
@collectionvarchar(128) ,
@idint ,
@namevarchar(128) ,
@subnamevarchar(128)
declare@contextvarchar(255) ,
@sqlvarchar(1000) ,
@rcint
if right(@OutFilePath,1) <> '\'
begin
select @OutFilePath = @OutFilePath + '\'
end
if right(@WorkPath,1) <> '\'
begin
select @WorkPath = @WorkPath + '\'
end
select@SourceDB = replace(replace(@SourceDB,'[',''),'[','')
select@ScriptType= 4 | 1 | 64 ,
@FileName= @OutFilePath + @OutFileName ,
@tmpFileName= @WorkPath + 'ScriptTmp.txt'
declare@objServerint ,
@objTransferint ,
@strResultvarchar(255) ,
@strCommandvarchar(255)
-- get objects to script and object type
create table #Objects (name varchar(128), subname varchar(128) default null, id int identity(1,1))
if @SourceObject is not null
begin
insert#Objects
(name)
select @SourceObject
end
if @ObjectType = 'TABLES'
begin
if @SourceObject is null
begin
select @sql = 'select TABLE_NAME, null '
select @sql = @sql + 'from[' + @SourceDB + '].INFORMATION_SCHEMA.TABLES '
select @sql = @sql + 'whereTABLE_TYPE = ''BASE TABLE'''
end
select @collection = 'tables'
end
else if @ObjectType in ('PROCS', 'PROCEDURES')
begin
if @SourceObject is null
begin
select @sql = 'select ROUTINE_NAME, null '
select @sql = @sql + 'from[' + @SourceDB + '].INFORMATION_SCHEMA.ROUTINES '
select @sql = @sql + 'whereROUTINE_TYPE = ''PROCEDURE'''
end
select @collection = 'storedprocedures'
end
else if @ObjectType = 'FUNCTIONS'
begin
if @SourceObject is null
begin
select @sql = 'select ROUTINE_NAME, null '
select @sql = @sql + 'from[' + @SourceDB + '].INFORMATION_SCHEMA.ROUTINES '
select @sql = @sql + 'whereROUTINE_TYPE = ''FUNCTION'''
end
select @collection = 'userdefinedfunctions'
end
else if @ObjectType = 'VIEWS'
begin
if @SourceObject is null
begin
select @sql = 'select TABLE_NAME, null '
select @sql = @sql + 'from[' + @SourceDB + '].INFORMATION_SCHEMA.VIEWS '
select @sql = @sql + 'whereTABLE_NAME not like ''sys%'''
end
select @collection = 'views'
end
else if @ObjectType = 'INDEXES'
begin
if @SourceObject is null
begin
select @sql = 'select o.name, i.name '
select @sql = @sql + 'from[' + @SourceDB + ']..sysobjects o, [' + @SourceDB + ']..sysindexes i '
select @sql = @sql + 'whereo.type = ''U'' '
select @sql = @sql + 'and i.id = o.id and i.indid <> 0 '
select @sql = @sql + 'and i.name not like ''_WA_%'''
select @sql = @sql + 'and o.name not like ''dtprop%'''
select @sql = @sql + 'and i.name not in (select name from [' + @SourceDB + ']..sysobjects)'
end
select @collection = 'tables'
end
else if @ObjectType = 'TRIGGERS'
begin
if @SourceObject is null
begin
select @sql = 'select o2.name, o.name '
select @sql = @sql + 'from[' + @SourceDB + ']..sysobjects o, [' + @SourceDB + ']..sysobjects o2 '
select @sql = @sql + 'whereo.xtype = ''TR'' '
select @sql = @sql + 'ando.parent_obj = o2.id '
end
select @collection = 'tables'
end
else if @ObjectType = 'DEFAULTS'
begin
if @SourceObject is null
begin
select @sql = 'select o.name, null '
select @sql = @sql + 'from[' + @SourceDB + ']..sysobjects o '
select @sql = @sql + 'where o.type = ''D'' and o.parent_obj = ''0'''
end
select @collection = 'Defaults'
end
else if @ObjectType = 'RULES'
begin
if @SourceObject is null
begin
select @sql = 'select o.name, null '
select @sql = @sql + 'from[' + @SourceDB + ']..sysobjects o '
select @sql = @sql + 'where type = ''R'''
end
select @collection = 'Rules'
end
else if @ObjectType = 'JOBS'
begin
if @SourceObject is null
begin
select @sql = 'select j.name, null '
select @sql = @sql + 'frommsdb..sysjobs j '
end
select @collection = 'jobs'
end
else if @ObjectType = 'DTS'
begin
select@sql = 'dtsrun /NScript_DTS_Packages /S(local) /E '
+ '/A"ServerName":8="' + @SourceSVR + '" '
+ '/A"Path":8="' + @OutFilePath + '" '
+ '/A"UserName":8="' + coalesce(@SourceUID,'') + '" '
+ '/A"Password":8="' + coalesce(@SourcePWD,'') + '" '
exec master..xp_cmdshell @sql
return
end
else
begin
select 'invalid @ObjectType'
return
end
if @SourceSVR <> @@servername
begin
select @sql = replace(@sql,'''','''''')
insert#Objects (name, subname) exec ('select * from openquery(' + @SourceSVR + ',''' + @sql + ''')')
end
else
begin
insert#Objects (name, subname) exec (@sql)
end
-- create empty output file
if @OutFileName is not null
begin
select@sql = 'echo. > ' + @FileName
exec master..xp_cmdshell @sql
end
--prepare scripting object
select @context = 'create dmo object'
exec @rc = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
if @rc <> 0 or @@error <> 0 goto ErrorHnd
if @SourceUID is null
begin
select @context = 'set integrated security ' + @SourceSVR
exec @rc = sp_OASetProperty @objServer, LoginSecure, 1
if @rc <> 0 or @@error <> 0 goto ErrorHnd
end
select @context = 'connect to server ' + @SourceSVR
exec @rc = sp_OAMethod @objServer , 'Connect', NULL, @SourceSVR , @SourceUID , @SourcePWD
if @rc <> 0 or @@error <> 0 goto ErrorHnd
select @context = 'scripting'
-- Script all the objects
select @id = 0
while exists (select * from #Objects where id > @id)
begin
select@id = min(id) from #Objects where id > @id
select @name = name, @subname = subname from #Objects where id = @id
if @OutFileName is null
begin
select@FileName = @OutFilePath + 'dbo."' + @name + coalesce('[' + @subname + ']','') + '.sql"'
select@sql = 'echo. > ' + @FileName
exec master..xp_cmdshell @sql
end
--select @sql = 'echo print ''Create = dbo.[' + @name + ']'+ coalesce('[' + @subname + ']','') + ''' >> ' + @FileName
--exec master..xp_cmdshell @sql
if @ObjectType = 'INDEXES'
begin
Set @sql = 'databases("' + @SourceDB + '").' + @collection + '("' + @name + '").indexes("' + @subname + '").script'
end
else if @ObjectType = 'TRIGGERS'
begin
Set @sql = 'databases("' + @SourceDB + '").' + @collection + '("' + @name + '").triggers("' + @subname + '").script'
end
else if @ObjectType = 'JOBS'
begin
Set @sql = 'Jobserver.Jobs("' + @name + '").Script'
end
else
begin
Set @sql = 'databases("' + @SourceDB + '").' + @collection + '("' + @name + '").script'
end
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, no_output
-- 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
=================================================
The output ( for ex. ) for a view looks like this:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[anothertest1]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[anothertest1]
GO <-----------------------------------THIS IS THE PART I DONT WANT IN THE txt DOCUMENTS
CREATE VIEW anothertest1
as
SELECT COUNT(1)test1 from test
where test1 = 'd'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
========================================
Does anyone know how to turn this off?
September 30, 2010 at 3:56 am
anyone?:ermm:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply