Inherriting objects from an updated MODEL database

CREATE PROC usp_CopyModelObjects AS

/*
*	PROC:-	usp_CopyModelObjects
*	Description:-	Copies objects present in the MODEL database
*					that are not yet present within the current database.
*					This applies to	DEFAULTS ,
									RULES ,
									VIEWS ,
									STORED PROCEDURES ,
									FUNCTIONS
*	Variables:-		@sObjectName	The name of the object to be created.
*					@sText01..20	Holds up to 20 records from the syscomments table
*									for any qualifying object.
*					@lObjectId		The unique id for the object to be copied.
*
*	REMARKS:-		It is assumed that the SQL statement will not exceed a maximum of 20
*					entries in the SysComments table.
*
*	Dependancies	Table:	Model.dbo.SysObjects
*							Model.dbo.SysComments
*							SysObjects
*
*	Date		Author		Description
*	===========	======		===========
*	01-Dec-2002	David Poole	Created
*/	
SET NOCOUNT ON
/*	Ensure that any concatenated nulls in a string still yield a string
*	This is to allow 20 syscomment records to be concatenated without resulting
*	in a null result, even though the majority of cases there will be only 1
*	syscomments entry
*/
SET CONCAT_NULL_YIELDS_NULL OFF	

DECLARE @sObjectName 	sysname ,
		@lObjectId		Int ,
		@sText01		VARCHAR(4000) ,
		@sText02		VARCHAR(4000) ,
		@sText03		VARCHAR(4000) ,
		@sText04		VARCHAR(4000) ,
		@sText05		VARCHAR(4000) ,
		@sText06		VARCHAR(4000) ,
		@sText07		VARCHAR(4000) ,
		@sText08		VARCHAR(4000) ,
		@sText09		VARCHAR(4000) ,
		@sText10		VARCHAR(4000) ,
		@sText11		VARCHAR(4000) ,
		@sText12		VARCHAR(4000) ,
		@sText13		VARCHAR(4000) ,
		@sText14		VARCHAR(4000) ,
		@sText15		VARCHAR(4000) ,
		@sText16		VARCHAR(4000) ,
		@sText17		VARCHAR(4000) ,
		@sText18		VARCHAR(4000) ,
		@sText19		VARCHAR(4000) ,
		@sText20		VARCHAR(4000)

	SET @sObjectName = ''

/*	Loop through the relevant sysobjects */
	WHILE @sObjectName IS NOT NULL
		BEGIN
			SELECT @sObjectName = MIN (modobj.Name)
			FROM	Model.dbo.sysobjects AS modobj LEFT JOIN dbo.sysobjects AS obj
				ON	modobj.name = obj.name
			WHERE	modobj.parent_obj=0 AND
					modobj.type IN ('d','fn','if','p','r','tf','v') AND
					modobj.name > @sObjectName AND
					obj.id IS NULL

			IF	@sObjectName IS NOT NULL
				BEGIN
					SELECT @lObjectId = Object_Id('model.dbo.'+@sObjectName)

/*	Populate the 20 text objects.	*/
					SELECT	@sText01 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=1

					SELECT	@sText02 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=2

					SELECT	@sText03 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=3

					SELECT	@sText04 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=4

					SELECT	@sText05 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=5

					SELECT	@sText06 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=6

					SELECT	@sText07 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=7

					SELECT	@sText08 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=8

					SELECT	@sText09 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=9

					SELECT	@sText10 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=10

					SELECT	@sText11 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=11

					SELECT	@sText12 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=12

					SELECT	@sText13 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=13

					SELECT	@sText14 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=14

					SELECT	@sText15 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=15

					SELECT	@sText16 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=16

					SELECT	@sText17 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=17

					SELECT	@sText18 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=18

					SELECT	@sText19 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=19

					SELECT	@sText20 = modcom.text			
					FROM	model.dbo.syscomments as modcom 
					WHERE 	modcom.id = @lObjectId and modcom.ColId=20

					EXECUTE ( 
						@sText01 +
						@sText02 +
						@sText03 +
						@sText04 +
						@sText05 +
						@sText06 +
						@sText07 +
						@sText08 +
						@sText09 +
						@sText10 +
						@sText11 +
						@sText12 +
						@sText13 +
						@sText14 +
						@sText15 +
						@sText16 +
						@sText17 +
						@sText18 +
						@sText19 +
						@sText20 )
				END
		END

GO
alter PROC Usp_CopyModelTypes AS
/*
*	PROC:-	Usp_CopyModelTypes
*	Description:-	Copies user defined types in the MODEL database
*					that are not yet present within the current database.
*					It also binds rules and defaults to those types.
*	Variables:-		@sSQLCommand		Holds the SQL Statement to be executed.
*					@sAllowNulls		Holds either 'NULL' or 'NOT NULL'
*					@sUserDefinedName	Holds the name of the user defined type
*					@sSystemName		Holds the name of the base type for the udt.
*					@sRuleName			Holds the name of the bound rule.
*					@sDefaultName		Holds the name of the bound default.
*					@lSize				Holds the size of the type.
*					@lScale				Holds the scale for use with NUMERIC and DECIMAL.
*					@bAllowNulls		Whether or not the type allows nulls.
*
*	REMARKS:-		It is assumed that the SQL statement will not exceed a maximum of 20
*					entries in the SysComments table.
*
*	Dependancies	Table:	Model.dbo.SysObjects
*							Model.dbo.SysTypes
*							SysTypes
*
*	Date		Author		Description
*	===========	======		===========
*	01-Dec-2002	David Poole	Created
*/	
	DECLARE	@sSQLCommand		VARCHAR(255) ,
			@sAllowNulls		CHAR(10),
			@sUserDefinedName	SysName ,
			@sSystemName		SysName ,
			@sRuleName			SysName ,
			@sDefaultName		SysName ,
			@lSize				SmallInt ,
			@lScale				SmallInt ,
			@bAllowNulls		bit

	DECLARE csr_Types CURSOR FOR
		SELECT	a.name As UserDefinedName , 
				b.name AS SystemName , 
				r.name AS RuleName, 
				d.name AS DefaultName,
				a.prec, 
				a.scale ,
				a.allownulls
		FROM	model.dbo.systypes as a INNER JOIN model.dbo.systypes as b
					ON	a.xtype = b.xusertype
		
				LEFT JOIN model.dbo.sysobjects as d
					ON	a.tdefault = d.id
		
				LEFT JOIN model.dbo.sysobjects as r
					ON	a.domain = r.id
		
				LEFT JOIN systypes as dbtype
					ON	a.name = dbtype.name
		WHERE	a.xtype <> a.xusertype
			AND	dbtype.name is null


	OPEN csr_Types

	FETCH NEXT FROM csr_Types INTO
			@sUserDefinedName	,
			@sSystemName		,
			@sRuleName			,
			@sDefaultName		,
			@lSize				,
			@lScale				,
			@bAllowNulls

	WHILE @@FETCH_STATUS = 0
		BEGIN

			IF @bAllowNulls = 1
				SET @sAllowNulls = '''NULL'''
			ELSE
				SET @sAllowNulls = '''NOT NULL'''
				
			SET @sSQLCommand='sp_AddType ' 
				+ @sUserDefinedName 
				+ ' , '''
				+ @sSystemName

			SELECT @sSQLCommand = @sSQLCommand
				+ CASE @sSystemName
				WHEN 'char'		THEN '(' + CAST(@lSize AS VARCHAR(10)) + ')'' ,' + @sAllowNulls
				WHEN 'varchar'	THEN '(' + CAST(@lSize AS VARCHAR(10)) + ')'' ,' + @sAllowNulls
				WHEN 'nchar'	THEN '(' + CAST(@lSize AS VARCHAR(10)) + ')'' ,' + @sAllowNulls
				WHEN 'nvarchar' THEN '(' + CAST(@lSize AS VARCHAR(10)) + ')'' ,' + @sAllowNulls
				WHEN 'decimal'	THEN '(' + CAST(@lSize AS VARCHAR(10)) + ',' + CAST(@lScale AS VARCHAR(3)) + ')'' ,' + @sAllowNulls 
				WHEN 'numeric'	THEN '(' + CAST(@lSize AS VARCHAR(10)) + ',' + CAST(@lScale AS VARCHAR(3)) + ')'' ,' + @sAllowNulls 
				ELSE ''' ,' + @sAllowNulls 
				END

			EXECUTE( @sSQLCommand)

			IF @sDefaultName IS NOT NULL
				BEGIN
					SET @sSQLCommand = 'sp_bindefault '''
						+ @sDefaultName
						+ ''' , '''
						+ @sUserDefinedName
						+''''
					EXECUTE (@sSQLCommand)
				END

			IF @sRuleName IS NOT NULL
				BEGIN
					SET @sSQLCommand = 'sp_bindrule '''
						+ @sRuleName
						+ ''' , '''
						+ @sUserDefinedName
						+''''
					EXECUTE (@sSQLCommand)
				END

			FETCH NEXT FROM csr_Types INTO
					@sUserDefinedName	,
					@sSystemName		,
					@sRuleName			,
					@sDefaultName		,
					@lSize				,
					@lScale				,
					@bAllowNulls
		END
		CLOSE csr_Types
		DEALLOCATE csr_Types

GO