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 toDEFAULTS ,
RULES ,
VIEWS ,
STORED PROCEDURES ,
FUNCTIONS
*Variables:-@sObjectNameThe name of the object to be created.
*@sText01..20Holds up to 20 records from the syscomments table
*for any qualifying object.
*@lObjectIdThe 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.
*
*DependanciesTable:Model.dbo.SysObjects
*Model.dbo.SysComments
*SysObjects
*
*DateAuthorDescription
*============================
*01-Dec-2002David PooleCreated
*/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 ,
@lObjectIdInt ,
@sText01VARCHAR(4000) ,
@sText02VARCHAR(4000) ,
@sText03VARCHAR(4000) ,
@sText04VARCHAR(4000) ,
@sText05VARCHAR(4000) ,
@sText06VARCHAR(4000) ,
@sText07VARCHAR(4000) ,
@sText08VARCHAR(4000) ,
@sText09VARCHAR(4000) ,
@sText10VARCHAR(4000) ,
@sText11VARCHAR(4000) ,
@sText12VARCHAR(4000) ,
@sText13VARCHAR(4000) ,
@sText14VARCHAR(4000) ,
@sText15VARCHAR(4000) ,
@sText16VARCHAR(4000) ,
@sText17VARCHAR(4000) ,
@sText18VARCHAR(4000) ,
@sText19VARCHAR(4000) ,
@sText20VARCHAR(4000)

SET @sObjectName = ''

/*Loop through the relevant sysobjects */WHILE @sObjectName IS NOT NULL
BEGIN
SELECT @sObjectName = MIN (modobj.Name)
FROMModel.dbo.sysobjects AS modobj LEFT JOIN dbo.sysobjects AS obj
ONmodobj.name = obj.name
WHEREmodobj.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
FROMmodel.dbo.syscomments as modcom 
WHERE modcom.id = @lObjectId and modcom.ColId=1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT@sText20 = modcom.text
FROMmodel.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:-@sSQLCommandHolds the SQL Statement to be executed.
*@sAllowNullsHolds either 'NULL' or 'NOT NULL'
*@sUserDefinedNameHolds the name of the user defined type
*@sSystemNameHolds the name of the base type for the udt.
*@sRuleNameHolds the name of the bound rule.
*@sDefaultNameHolds the name of the bound default.
*@lSizeHolds the size of the type.
*@lScaleHolds the scale for use with NUMERIC and DECIMAL.
*@bAllowNullsWhether 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.
*
*DependanciesTable:Model.dbo.SysObjects
*Model.dbo.SysTypes
*SysTypes
*
*DateAuthorDescription
*============================
*01-Dec-2002David PooleCreated
*/DECLARE@sSQLCommandVARCHAR(255) ,
@sAllowNullsCHAR(10),
@sUserDefinedNameSysName ,
@sSystemNameSysName ,
@sRuleNameSysName ,
@sDefaultNameSysName ,
@lSizeSmallInt ,
@lScaleSmallInt ,
@bAllowNullsbit

DECLARE csr_Types CURSOR FOR
SELECTa.name As UserDefinedName , 
b.name AS SystemName , 
r.name AS RuleName, 
d.name AS DefaultName,
a.prec, 
a.scale ,
a.allownulls
FROMmodel.dbo.systypes as a INNER JOIN model.dbo.systypes as b
ONa.xtype = b.xusertype

LEFT JOIN model.dbo.sysobjects as d
ONa.tdefault = d.id

LEFT JOIN model.dbo.sysobjects as r
ONa.domain = r.id

LEFT JOIN systypes as dbtype
ONa.name = dbtype.name
WHEREa.xtype <> a.xusertype
ANDdbtype.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