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.