if exists (select * from sysobjects where name = 'sp_LTI_ScriptDB' and type = 'P')
begin
	drop Procedure sp_LTI_ScriptDB
end
go
create procedure sp_LTI_ScriptDB 
@dbname sysname = NULL			
as

declare @exec_stmt nvarchar(625)
declare @showdev	bit
declare @allstatopts	int
declare @allrelstatopts	int
declare @allcatopts	int
declare @name           sysname
declare @curdbid int

set nocount on



/*	Create temp table before any DMP to enure dynamic
**  Since we examine the status bits in sysdatabase and turn them
**  into english, we need a temporary table to hold the descriptions.
*/
create table #spdboption
(
	dboption	varchar(255)	null
)

	

/*
**  If no database name given, raise an error
*/
if @dbname is null
begin
	raiserror ('You must specify the database name', 16, 1)
	return
end
select @showdev = 1

/*
**  See if the database exists
*/
select @curdbid=dbid from master.dbo.sysdatabases 
	where (name = @dbname)
if @curdbid is null
	begin
		raiserror(15010,-1,-1,@dbname)
		return (1)
	end
/*
** Get bitmap of all options that can be set by sp_dboption.
*/
select @allstatopts=number from master.dbo.spt_values
	where type = 'D' and name = 'ALL SETTABLE OPTIONS'
select @allrelstatopts=number from master.dbo.spt_values
	where type = 'D2' and name = 'ALL SETTABLE OPTIONS'
select @allcatopts=number from master.dbo.spt_values
	where type = 'DC' and name = 'ALL SETTABLE OPTIONS'




/* 
** Check if you have access to database
*/

	if (has_dbaccess(@dbname) <> 1)
	begin
	  raiserror(15622,-1,-1, @dbname)
	  return
	end




/*
**  build the database status
**  description.
*/
declare @bitdesc varchar(35)	/* db option English description */

	
	/*
	** First check bits in sysdatabases.status.
	*/

	/*
	**  Check select into/bulk copy bit (4)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'D'
			and d.status & v.number = 4
			and v.number <> @allstatopts       /* all status options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end

	/*
	**  Check truncate log on checkpoint bit (8)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'D'
			and d.status & v.number = 8
			and v.number <> @allstatopts       /* all status options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end


	/*
	**  Check no checkpoint on recovery bit (16)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'D'
			and d.status & v.number = 16
			and v.number <> @allstatopts /* all status options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end


	/*
	**  Check don't recover bit (32)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'D'
			and d.status & v.number = 32
			and v.number <> @allstatopts       /* all status options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end


	/*
	**  Check not recovered only bit (256)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'D'
			and d.status & v.number = 256
			and v.number <> @allstatopts       /* all status options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end


	/*
	**  Check offline bit (512)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'D'
			and d.status & v.number = 512
			and v.number <> @allstatopts       /* all status options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end


	/*
	**  Check read only bit (1024)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'D'
			and d.status & v.number = 1024
			and v.number <> @allstatopts       /* all status options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end


	/*
	**  Check dbo only bit (2048)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'D'
			and d.status & v.number = 2048
			and v.number <> @allstatopts       /* all status options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end

	/*
	**  Check single user bit (4096)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'D'
			and d.status & v.number = 4096
			and v.number <> @allstatopts       /* all status options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end

	/*
	**  Check ANSI NULL default bit (16384)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'D2'
			and d.status & v.number = 16384
			and v.number <> @allrelstatopts       /* all status options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end


	/*
	**  Check ANSI empty string bit (65536)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'D2'
			and d.status & v.number = 65536
			and v.number <> @allrelstatopts       /* all status options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end


	/*
	**  Check recursive triggers bit (131072)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'D2'
			and d.status & v.number = 131072
			and v.number <> @allrelstatopts       /* all status options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end


	/*
	** Now check bits in sysdatabases.category.
	*/

	/*
	**  Check published bit (1)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'DC'
			and d.category & v.number = 1
			and v.number <> @allcatopts       /* all options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end



	/*
	**  Check subscribed bit (2)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'DC'
			and d.category & v.number = 2
			and v.number <> @allcatopts       /* all options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end


	/*
	**  Check merge publish bit (4)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'DC'
			and d.category & v.number = 4
			and v.number <> @allcatopts       /* all options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end


	/*
	**  Check distributed bit (16)
	*/
	select @bitdesc = null
	select @bitdesc = v.name
		from master.dbo.spt_values v, master.dbo.sysdatabases d
		where d.dbid = @curdbid
			and v.type = 'DC'
			and d.category & v.number = 16
			and v.number <> @allcatopts       /* all options */
	if @bitdesc IS NOT NULL
	begin
		insert #spdboption (dboption) values (@bitdesc)
	end


/* store the list of files */
create table #spFileList
(name sysname,
 fileid int,
 filename nchar(260),
 filegroup sysname null,
 size nvarchar(15),
 maxsize nvarchar(15),
 growth nvarchar(15),
 usage nvarchar(10),
 status int) 

declare @cmd varchar(8000) 
/* need to get the list of files for the specific database, so must exec the insert */
select @exec_stmt = 'insert into #spfiledesc
			select db_id (N'+ quotename(@name, '''') + '), fileid, status, size, name from ' 
			 + quotename(@name, '[') + '.dbo.sysfiles'
	execute (@exec_stmt)
select @cmd = 'Insert #spFileList(name, fileid, filename, filegroup, size, maxsize, growth, usage, status)'
	      + 'select name,  fileid, filename, groupname, convert(nvarchar(15), size * 8) + N'' KB'', '
	      + '(case maxsize when -1 then N''Unlimited''' 
	      + 'else '
	      + 'convert(nvarchar(15), maxsize * 8) + N'' KB'' end), '
	      + '(case ' + quotename(@dbname, '[') + '.dbo.sysfiles.status & 0x100000 when 0x100000 then '
	      + 'convert(nvarchar(3), growth) + N''%'' '
	      + 'else '
	      + 'convert(nvarchar(15), growth * 8) + N'' KB'' end), '
	      + '(case ' + quotename(@dbname, '[') + '.dbo.sysfiles.status & 0x40 when 0x40 then ''log only'' else ''data only'' end), '
	      + quotename(@dbname, '[') + '.dbo.sysfilegroups.status '
	      + 'from ' + quotename(@dbname, '[') + '.dbo.sysfiles'
	      + ' left outer join ' + quotename(@dbname, '[') + '.dbo.sysfilegroups'
	      + ' on ' + quotename(@dbname, '[') +'.dbo.sysfiles.groupid = '
	      + quotename(@dbname, '[') + '.dbo.sysfilegroups.groupid'

exec (@cmd)
/*select substring(@cmd, 1, 255)
select substring(@cmd, 256, 255)
select substring(@cmd, 512, 255)
select * from #spFileList */
/* create a temporary table to hold the code. This allows us to remove (painfully) extraneous
   commas */
create table #TheCode
(TheKey int Identity,
	TheLine varchar(8000))
create unique index #TheCode_IDX on #TheCode (TheKey)
/* now begin building the Create Statement */
Insert #TheCode(TheLine)
SELECT 'CREATE DATABASE [' + @dbname + ']'
insert #TheCode(TheLine)
SELECT 'ON PRIMARY ' 

/* generate the filespecs for the primary file group */	
	insert #TheCode(TheLine)
	select   '(NAME = ' + rtrim(name) + ', FILENAME = ''' + rtrim(filename)
		 + ''', SIZE = ' + size + ', MAXSIZE = ' + maxsize
		 + ' , FILEGROWTH = ' + growth + '), '  
		from #spFileList
	where upper(FileGroup) = N'PRIMARY'
	

declare @filegroup sysname
/* build a list of secondary file group names */
declare SecondaryFG cursor for select DISTINCT filegroup from #spFileList 
	where FileGroup <> N'Primary'
Open SecondaryFG
fetch SecondaryFG into @filegroup
while @@FETCH_STATUS <> -1
begin
	insert #TheCode(TheLine)
	SELECT 'FILEGROUP [' + rtrim(@filegroup) + ']'
	insert #TheCode(TheLine)
	select  '(NAME = ' + rtrim(name) + ', FILENAME = ''' + rtrim(filename)
		 + ''', SIZE = ' + size + ', MAXSIZE = ' + maxsize
		 + ' , FILEGROWTH = ' + growth + '), '
	from #spFileList
	where upper(FileGroup) = @filegroup
	and usage = N'data only'
	fetch SecondaryFG into @filegroup
end
close SecondaryFG
Deallocate SecondaryFG
/* and now deal with the log files */
insert #TheCode(TheLine)
SELECT 'LOG ON '
insert #TheCode(TheLine)
select  '(NAME = ' + rtrim(name) + ', FILENAME = ''' + rtrim(filename)
	  + ''', SIZE = ' + size + ', MAXSIZE = ' + maxsize
	  + ' , FILEGROWTH = ' + growth + '),'
	from #spFileList
	where usage = N'log only'

/* now deal with trailing commas */

declare CommaProc  cursor scroll for select TheLine from #TheCode FOR UPDATE
declare @TheLine varchar(8000)
Open CommaProc
Fetch CommaProc into @TheLine
If @@Fetch_Status <> -1
begin
/* find the LOG ON line so that we can remove the comma from its predecessor */
	While @TheLine not like 'LOG %'
	begin
		fetch Next from CommaProc into @TheLine
	end
	/* now we should be sitting on the LOG ON line; get the prev line */
	Fetch Prior from CommaProc into @TheLine
	select @TheLine = substring(@TheLine, 1, LEN(@TheLine) - 1)
	update #TheCode
		set TheLine = @TheLine where Current of CommaProc
	/* now there's one more comma to deal with on the last line of the Log files */
	Fetch last from CommaProc into @TheLine
	select @TheLine = substring(@TheLine, 1, LEN(@TheLine) - 1)
		update #TheCode
		set TheLine = @TheLine where Current of CommaProc
end
close CommaProc
deallocate CommaProc

select TheLine from #TheCode

select 'go'
/* Now generate file group properties */
/* output default file group, if any */
select distinct 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP ' + filegroup + ' DEFAULT'
from #spFileList
where status = 16 and filegroup <> N'PRIMARY'
select 'go'
/* output read-only filegroups, if any */
select 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP ' + filegroup + ' READONLY'
from #spFileList
where status = 8 
select 'go'

/* now generate db_option settings */
select 'exec sp_dboption [' + @dbname + '], ''' + dboption + ''', true' + CHAR(13)+char(10) + 'go' 
	from #spdboption


return (0)