Inherriting objects from an updated MODEL database

,

If new objects are created in the model database then these new objects only get created for new databases.
Similarly, if objects are removed from user databases then getting them back into the database can be a pain.
The following two stored procs copy objects from model to the current database if they do not already exist.

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

Rate

Share

Share

Rate