Automating “Generate SQL Script”

MAK examines how to automate the "Generate SQL
Script" by using a stored procedure to take advantage of "scptxfr.exe"
provided by Microsoft.

Step 1

Create the
folder "MSSQLScripts" on the SQL Server box’s hard disk drive.

Eg:

MKDIR d:\MSSQLScripts

Step 2

Execute
the source code below in query analyzer. [refer Fig:1.1] This will create the
procedure "sp__GenerateScript."

Source Code


set quoted_identifier off
go
use master
if exists (select * from dbo.sysobjects where id =
object_id(N’[dbo].[sp__GenerateScript]’)
and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[sp__GenerateScript]
GO
create proc sp__GenerateScript
@dbname varchar(256) = ”,
@Applicationpath varchar(700) =
‘C:\program files\Microsoft SQL Server\mssql\upgrade\’,
@destinationpath varchar(700) =’D:\MSSQLScripts\’,
@switches varchar(200) = ‘ /X /Y /A /q /r /G /I ‘
as
set quoted_identifier off
set nocount on
set concat_null_yields_null off
declare @count int
declare @folderexist int
declare @maxcount int
declare @query varchar(1000)
declare @date varchar(10)
set @date = convert(varchar(10),getdate(),112)
set @count =1
Print ‘Generate Script – Started’
print getdate()
set @Applicationpath = @Applicationpath +’scptxfr.exe’
create table #dbtable (id int identity(1,1), dbname varchar(256))
if @dbname = ”
begin
insert into #dbtable (dbname) select name from
master..sysdatabases
where name != ‘tempdb’ and status & 32 != 32
and status & 256 != 256 and status & 512 != 512
and status & 1024 != 1024 and status & 4096 != 4096
and status & 32768 !=32768 and status & 1073741824 !=1073741824
end
else
begin
insert into #dbtable (dbname) select name from
master..sysdatabases
where name != ‘tempdb’ and status & 32 != 32
and status & 64 != 64 and status & 128 != 128
and status & 256 != 256 and status & 512 != 512
and status & 1024 != 1024 and status & 4096 != 4096
and status & 32768 !=32768 and status & 1073741824 !=1073741824
and name = @dbname
end

if (select count(*) from #dbtable) = 0
begin
set @date = convert(varchar(100), getdate(),109)
Print ‘Error: No valid database found for Generating Script’
end
else
begin
set @destinationpath = @destinationpath +@date
create table #files (Files int, Folder int, parent int)
insert #files exec master.dbo.xp_fileexist @destinationpath
select @folderexist = Folder from #files
if @folderexist <>1
begin
set @query = ‘MKDIR “‘+@destinationpath+'”‘
print @query
exec master..xp_cmdshell @query
set @destinationpath = @destinationpath
end
else
begin
print ‘Information:’+ @destinationpath + ‘ already exist. Skipping Folder Creation’
end
set @maxcount = (select max(id) from #dbtable)

While @count <= @maxcount
begin
set @dbname = (select dbname from #dbtable where id = @count)
set @query = ‘”‘+@applicationpath +'”‘+ ‘ /s ‘+@@servername+ ‘
/d ‘+@dbname+ +’ /F ‘+@destinationpath+ @switches
set @query = @query
print @query
exec master..xp_cmdshell @query

if @@error <> 0
begin
Print ‘Error’
end

set @count = @count+1
end

end
print getdate()
Print ‘Generate Script – Completed’

–Usage:
–exec sp__GenerateScript ”,’D:\program files\Microsoft SQL Server\mssql\upgrade\’,’D:\mssqlscripts\’
–exec sp__GenerateScript
–exec sp__GenerateScript ‘MSDB’,’C:\program files\Microsoft SQL Server\mssql\upgrade\’,’D:\mssqlscripts\’
–exec sp__GenerateScript ‘TOM’,’C:\program files\Microsoft SQL Server\mssql\upgrade\’,’D:\mssqlscripts\’

–xp_cmdshell ‘”D:\program files\Microsoft SQL Server\mssql\upgrade\scptxfr.exe” /s SQL
/d Anand /F D:\mssqlscripts\20040827 /X /Y /A /q /r /G /I’

Download
the above stored procedure here.

Fig 1.1

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles