SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dmoScriptTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dmoScriptTables]
GO



create procedure dbo.dmoScriptTables
	@pDatabaseName varchar(255),
	@pTempFolder varchar(255) = null,
        @pInstanceName varchar(30) = null
as
/**********************************************************************************
Script table collections using DMO object model granularity. By default, scripts 
are placed in the dbChangeControl working folders of specified database on this SQL 
Server instance. By providing an alternate valid path to the cloaked tempFolder parameter
the scripts can be placed in another location. Additionally, by providing an instance
name to the cloaked Instance Parameter, another instance on the local machine can be 
scripted provided it is established in the metadata tables. 

Notes. Use with dmoScriptDatabse to get database collections. The specified database 
on the local server must be a member of a healthy project. If an attempt is made to 
script to the working folders of a checked in project, the scripting will fail 
because the existing files are read only. 
***********************************************************************************/
-- common
declare @dmoServer int,
	@path varchar(255),
	@workingFolder varchar(255),
	@cmd varchar(1200),
	@returnstatus int,
	@dmoMethod varchar(255), 
	@dmoProperty varchar(255), 
	@dmoCollection varchar(255), 
	@scriptFile varchar(255),
	@hr int,
	@oleErrorSource varchar(255),
	@oleErrorDescription varchar(1000),
	@scriptType int,
	@procedureName sysname,
	@vssUserName varchar(30),
	@vssShare varchar(255),
--tables
	@isSystemTable tinyint,
	@tableCount int,
	@curTableNb int,
	@tableName varchar(256),
	@tableScriptType int,
	@permissionsScriptType int,
        @firstTable bit,
        @permissionsScriptFile varchar(255),
-- keys
	@keyCount int,
	@curKeyNb int,
	@keyName varchar(255),
	@keyType int,
-- dridefaults
	@columnCount int,
	@curColumnNb int,
	@objectName varchar(255),
	@DRIDefaultName varchar(255),
-- indexes
	@indexCount int,
	@curIndexNb int,
	@indexName varchar(256),
	@indexType int,
-- checks
	@checkCount int,
	@curCheckNb int,
	@checkName varchar(256),
-- triggers
	@triggerCount int,
	@curTriggerNb int,
	@triggerName varchar(256)

set nocount on

-- init
set @procedureName = db_name() + '.' 
			+ user_name(objectproperty(@@procid,'OwnerId')) 
                	+ '.' + object_name(@@procid)

-- get file system path 
select @workingFolder 	= s.workingFolderRoot 
			+ p.projectName + '\'
			+ e.environmentName + '\'
			+ s.serverName + '\' 
			+ case when (@pInstanceName is null) then s.instanceName 
                                else @pInstanceName
                                end
                        + '\'
			+ @pDatabaseName + '\Tables\',
	@vssUserName = n.vssUserName
from Admin.dbo.SqlDatabase d 
inner join Admin.dbo.ProjectNode n
on d.id = n.sqlDatabase_id
inner join .SQlServer s
on s.id = n.sqlServer_id
inner join Admin.dbo.Environment e
on e.id = n.environment_id
inner join Admin.dbo.Project p 
on p.id = n.project_id
where d.databaseName = @pDatabaseName
--and s.id = Admin.dbo.thisSQLServer_id() cannot use function in SQL Server 7
and s.serverName = reverse(substring(reverse(@@servername), 
				charindex('\',reverse(@@servername)) + 1, 
                        	datalength(reverse(@@servername))))
and s.instanceName = case when (@pInstanceName is null) 
                        then substring(@@servername, 
       		       	                charindex('\',@@servername) + 1, 
               		                datalength(@@servername))
                        else @pInstanceName
                        end
and n.IsActive = 1

if @@rowcount = 0
	begin
		raiserror(59002,16,1,@procedureName, @pDatabaseName)
		return -1
	end

-- wrong user asking
if substring(system_user,charindex('\', system_user) + 1, datalength(system_user)) <> @vssUserName
	begin
		set @vssUserName = substring(system_user,charindex('\', system_user) + 1, datalength(system_user))
		set @vssShare = '' -- don't tell em
		raiserror (59005,16,1, @procedurename, 	@vssUserName, @vssShare)
		goto ErrorHandler
	end

-- file system project working folder must exist
set @cmd = 'dir ' + @workingFolder
exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
if @returnstatus <> 0 
	begin
		raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
		goto ErrorHandler
	end

if (@pTempFolder is not null)
	begin
		-- file system temporary working folder must exist
		set @cmd = 'dir ' + @pTempFolder
		exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
		if @returnstatus <> 0 
			begin
				raiserror (59007,16,1,@procedureName, @pTempFolder,@@servername)
				goto ErrorHandler
			end

		select @path = case when (substring(@pTempFolder,
							datalength(@pTempFolder),
							1) = '\')
					then @pTempFolder + @pDatabaseName + '\Tables\'
					else @pTempFolder + '\' + @pDatabaseName + '\Tables\'
					end 
		
		-- clear the temp area and copy the place empty hierarchy folders 
		set @cmd = 'rmdir /S /Q ' + @path 
		exec @returnstatus = master.dbo.xp_cmdshell @CMD, no_output
		-- if real error it will be handles in next step

		-- replicate working folder hierarchy to temp area
		set @CMD = 'xcopy ' + @workingFolder + '*.* ' 
			 + @path + ' /T /E /Y'
		exec @returnstatus = master..xp_cmdshell @CMD, NO_OUTPUT
		if @returnstatus <> 0 
			goto ErrorHandler
		
	end 
else
	begin
		set @path = @workingFolder

	end

-- table script options
set @tableScriptType 	        = 4 		-- primary object
        		        + 64 		-- to
	        		+ 512		-- no DRI 
		        	+ 4096 		-- if not exists
			        + 262144	-- owner qualify

-- permissions script options
set @permissionsScriptType 	= 2 		-- object permissions
		        	+ 32 		-- database (statement) permissions
--                              + 256           -- append (add this after first table inits the file)  
set @permissionsScriptFile = @path + 'allTablePermissions.sql'
set @firstTable = 0

-- constraint, index and trigger script options
set @scriptType         	= 1		-- drop 
			        + 4		-- primary object
        			+ 64		-- to file only
	        		+ 4096		-- if not exists
		        	+ 262144	-- owner qualify

-- open an in-process COM/DMO connection to this server
exec @hr = master..sp_OACreate 'SQLDMO.SQLServer', @dmoServer OUT
if @hr <> 0 goto ErrorHandler

-- set the security context to integrated 
exec @hr = master..sp_OASetProperty @dmoServer,'loginSecure',1
if @hr <> 0 goto ErrorHandler

-- connect to the specified server 
exec @hr = master..sp_OAMethod @dmoServer,'Connect',NULL,@@servername
if @hr <> 0 goto ErrorHandler

-- script each table and table child object to a separate file
select @dmoProperty = 'Databases("' 
			+ @pDatabaseName 
			+ '").Tables.Count'
exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
					@dmoProperty,
					@tableCount OUT
if @hr <> 0 goto ErrorHandler
select @curTableNb = 1
while @curTableNb <= @tableCount   
	begin
		-- refresh the DMO buffers each iteration
		select @dmoMethod 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").Tables.Refresh(TRUE)'
		exec @hr = master.dbo.sp_OAMethod 	@dmoServer,
							@dmoMethod,
							NULL
		if @hr <> 0 goto ErrorHandler

		select @dmoProperty 	= 'Databases("' 
					+ @pDatabaseName 
					+ '").Tables.Item(' 
					+ cast(@curTableNb as varchar(10)) 
					+ ').SystemObject'
		exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
							@dmoProperty,
							@isSystemTable OUT
		if @hr <> 0 goto ErrorHandler
		-- only script user table
		if @isSystemTable = 0 
			begin
				-- get the table name 
				select @dmoProperty 	= 'Databases("' 
							+ @pDatabaseName 
							+ '").Tables.Item(' 
							+ cast(@curTableNb as varchar(10)) 
							+ ').Name'
				exec @hr = master.dbo.sp_OAGetProperty 	@dmoServer,
									@dmoProperty,
									@tableName OUT
				if @hr <> 0 goto ErrorHandler
				select @dmoMethod = 'Databases("' 
						+ @pDatabaseName 
						+ '").Tables("' 
						+ @tableName 
						+ '").Script'

				-- put each table script in its own file 
				select @scriptFile = @path + @tableName + '.sql'
				exec @hr = master.dbo.sp_OAMethod @dmoServer,
								@dmoMethod,
								NULL,
								@tableScriptType,
								@scriptFile
				if @hr <> 0 goto ErrorHandler

				-- put all table permissions in one file 
				exec @hr = master.dbo.sp_OAMethod @dmoServer,
								@dmoMethod,
								NULL,
								@permissionsScriptType,
								@permissionsScriptFile
				if @hr <> 0 goto ErrorHandler
                                -- append all remaining permissions to this file
                                if @firstTable = 0
                                        begin
                                                set @permissionsScriptType = @permissionsScriptType + 256
                                                set @firstTable = 1
                                        end
                                
				--keys
				set @keyType = 0
				select @dmoProperty = 'Databases("' + 
							@pDatabaseName 
							+ '").Tables("' 
							+ @tableName 
							+ '").Keys.Count'
				exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
									@dmoProperty,
									@keyCount OUT
				if @hr <> 0 goto ErrorHandler
				set @curKeyNb = 1
				while @curKeyNb <= @keyCount 
					begin
						select @dmoProperty = 'Databases("' 
									+ @pDatabaseName 
									+ '").Tables("' 
									+ @tableName 
									+ '").Keys(' 
									+ cast(@curKeyNb as varchar(10)) 
									+ ').type'
						exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
											@dmoProperty,
											@keyType OUT
						if @hr <> 0 goto ErrorHandler
						select @dmoProperty = 'Databases("' 
									+ @pDatabaseName 
									+ '").Tables("' 
									+ @tableName 
									+ '").Keys(' 
									+ cast(@curKeyNb as varchar(10)) 
									+ ').Name'
						exec @hr = master.dbo.sp_OAGetProperty 
								@dmoServer,
								@dmoProperty,
								@keyName OUT                
						if @hr <> 0 goto ErrorHandler
						if @keyName <> ''
							begin
								select @dmoMethod = 'Databases("' 
										+ @pDatabaseName 
										+ '").Tables("' 
					                                        + @tableName 
										+ '").Keys(' 
										+ cast(@curKeyNb as varchar(10)) 
					                                        + ').Script'
								if @keyType = 1   
									set @scriptFile = @path 
										+ 'PrimaryKeys\' 
										+ @keyName 
										+ '.sql'
								if @keyType = 2
									set @scriptFile = @path 
											+ 'UniqueKeys\' 
											+ @keyName 
											+ '.sql'
								if @keyType = 3
										set @scriptFile = @path 
											+ 'ForeignKeys\' 
											+ @keyName + '.sql'
								exec @hr = master.dbo.sp_OAMethod @dmoServer,
											@dmoMethod,
											NULL,
											@scriptType,
												@scriptFile
								if @hr <> 0 goto ErrorHandler
							end
						set @curKeyNb = @curKeyNb + 1
					end 

				--DRI Defaults
				set @curColumnNb = 1
				select @dmoProperty = 'Databases("' 
							+ @pDatabaseName 
							+ '").Tables("' 
							+ @tableName 
							+ '").Columns.Count'
				exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
									@dmoProperty,
									@columnCount OUT                
				if @hr <> 0 goto ErrorHandler
				while @curColumnNb <= @columnCount
					begin
				                select @dmoProperty = 'Databases("' 
									+ @pDatabaseName 
									+ '").Tables("' 
                        						+ @tableName 
									+ '").Columns(' 
									+ cast(@curColumnNb as varchar(10)) 
									+ ').DRIDefault.Name'
                				exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
											@dmoProperty,
											@DRIDefaultName OUT                
				                if @hr <> 0 goto ErrorHandler
				                if @DRIDefaultName <> ''
				                        begin
                                				select @scriptFile = @path 
										+ 'Defaults\' 
										+ @DRIDefaultName 
										+ '.sql' 
				                                select @dmoMethod = 'Databases("' 
											+ @pDatabaseName 
											+ '").Tables("' 
						                                        + @tableName 
											+ '").Columns(' 
											+ cast(@curColumnNb as varchar(10)) 
						                                        + ').DRIDefault.Script'
				                                exec @hr = master.dbo.sp_OAMethod @dmoServer,
												@dmoMethod,
												NULL,
												@scriptType,
												@scriptFile
				                                if @hr <> 0 goto ErrorHandler
				                        end
				                set @curColumnNb = @curColumnNb + 1
				        end

				--Indexes
				set @curIndexNb = 1
				select @dmoProperty = 'Databases("' 
							+ @pDatabaseName 
							+ '").Tables("' 
							+ @tableName 
							+ '").Indexes.Count'
				exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
									@dmoProperty,
									@indexCount OUT
				if @hr <> 0 goto ErrorHandler
				while @curIndexNb <= @indexCount
        				begin
						select @dmoProperty = 'Databases("' 
									+ @pDatabaseName 
									+ '").Tables("' 
									+ @tableName 
									+ '").Indexes(' 
									+ cast(@curIndexNb as varchar(10)) 
									+ ').Type'
						exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
											@dmoProperty,
											@indexType OUT
						if @hr <> 0 goto ErrorHandler
						-- don't script Primary Keys or Unique Constraints here
						if (not(@indexType & 2048 = 2048) 
						and not(@indexType & 4096 = 4096))
							begin
								select @dmoProperty = 'Databases("' 
											+ @pDatabaseName 
											+ '").Tables("' 
											+ @tableName 
											+ '").Indexes(' 
											+ cast(@curIndexNb as varchar(10)) 
											+ ').Name'
								exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
													@dmoProperty,
													@indexName OUT
								if @hr <> 0 goto ErrorHandler
								select @scriptFile = @path 
										+ 'Indexes\' 
										+ @indexName 
										+ '.sql' 
								select @dmoMethod = 'Databases("' 
										+ @pDatabaseName 
										+ '").Tables("' 
										+ @tableName 
										+ '").Indexes(' 
										+ cast(@curIndexNb as varchar(10)) 
										+ ').Script'
								exec @hr = master.dbo.sp_OAMethod @dmoServer,
												@dmoMethod,
												NULL,
												@scriptType,
												@scriptFile
								if @hr <> 0 goto ErrorHandler
							end
						set @curIndexNb = @curIndexNb + 1
					end

				--checks
				set @curCheckNb = 1
				select @dmoProperty = 'Databases("' 
							+ @pDatabaseName 
							+ '").Tables("' 
							+ @tableName 
							+ '").Checks.Count'
				exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
									@dmoProperty,
									@checkCount OUT
				if @hr <> 0 goto ErrorHandler
				while @curCheckNb <= @checkCount
					begin
						select @dmoProperty = 'Databases("' 
									+ @pDatabaseName 
									+ '").Tables("' 
									+ @tableName 
									+ '").Checks(' 
									+ cast(@curCheckNb as varchar(10)) 
									+ ').Name'
						exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
											@dmoProperty,
											@checkName OUT
						if @hr <> 0 goto ErrorHandler
						select @scriptFile = @path 
								+ 'Checks\' 
								+ @CheckName 
								+ '.sql' 
						select @dmoMethod = 'Databases("' 
									+ @pDatabaseName 
									+ '").Tables("' 
									+ @tableName 
									+ '").Checks(' 
									+ cast(@curCheckNb as varchar(10)) 
									+ ').Script'
						exec @hr = master.dbo.sp_OAMethod @dmoServer,
										@dmoMethod,
										NULL,
										@scriptType,
										@scriptFile
						if @hr <> 0 goto ErrorHandler
						set @curCheckNb = @curCheckNb + 1
					end

				--triggers
				set @curTriggerNb = 1
				select @dmoProperty = 'Databases("' 
							+ @pDatabaseName 
							+ '").Tables("' 
							+ @tableName 
							+ '").Triggers.Count'
				exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
									@dmoProperty,
									@triggerCount OUT
				if @hr <> 0 goto ErrorHandler
				while @curTriggerNb <= @triggerCount
					begin
						select @dmoProperty = 'Databases("' 
									+ @pDatabaseName 
									+ '").Tables("' 
									+ @tableName 
									+ '").Triggers(' 
									+ cast(@curTriggerNb as varchar(10)) 
									+ ').Name'
						exec @hr = master.dbo.sp_OAGetProperty @dmoServer,
											@dmoProperty,
											@triggerName OUT
						if @hr <> 0 goto ErrorHandler
						select @scriptFile = @path 
								+ 'Triggers\' 
								+ @triggerName 
								+ '.sql' 
						select @dmoMethod = 'Databases("' 
								+ @pDatabaseName 
								+ '").Tables("' 
								+ @tableName 
								+ '").Triggers(' 
								+ cast(@curTriggerNb as varchar(10)) 
								+ ').Script'
						exec @hr = master.dbo.sp_OAMethod @dmoServer,
										@dmoMethod,
										NULL,
										@scriptType,
										@scriptFile
						if @hr <> 0 goto ErrorHandler

						set @curTriggerNb = @curTriggerNb + 1
					end
			end
		select @curTableNb = @curTableNb + 1
	end

-- close and cleanup the COM/DMO database connection 
exec @hr = master.dbo.sp_OAMethod @dmoServer,'DisConnect'
if @hr <> 0 goto ErrorHandler

exec @hr = master.dbo.sp_OADestroy @dmoServer
if @hr <> 0 goto ErrorHandler

-- audit completion
insert Admin.dbo.ActivityLog (activity)
select @procedureName + ' ' + @pDatabaseName + ' completed normally'

return

ErrorHandler:
if (@hr is not null)
	begin	
		exec master.dbo.sp_OAGetErrorInfo 	@dmoServer, 
							@OleErrorSource OUT, 
							@OleErrorDescription OUT
		insert Admin.dbo.ActivityLog (activity)
		select @procedureName + ' ' + @pDatabaseName 
			+ ' ended with error: ' + cast(@hr as varchar(20)) + '
			OLE ERROR: ' 
		--+ isnull(Admin.dbo.binToHex (@hr),'not defined') no function in 7 so skip conversion of error number
		+ cast(@hr as varchar(20)) + '
			Source: ' + isnull(@OleErrorSource,'unknown') + '
			Description: ' + isnull(@OleErrorDescription,'unknown')

		-- still need to cleanup
		exec master.dbo.sp_OAMethod @dmoServer,'DisConnect'
		exec master.dbo.sp_OADestroy @dmoServer

		raiserror (59001,16,1,@procedureName)
	end
else
	if @cmd is not null
		begin
			insert Admin.dbo.ActivityLog (activity)
			select @procedureName + ' ' + @pDatabaseName 
				+ ' ' + isNull(@pTempFolder,'') 
				+ ' failed with returnstatus ' 
				+ cast(@returnstatus as varchar(10)) + ' at: ' + @cmd
			raiserror(59001,16,1,@procedureName)
		end
	else
		raiserror (59000,16,1,@procedureName)

return -1


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO