Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 1, 2004

Automating "Generate SQL Script"

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date