Compare the structure of two tables.

,

@strUser_nm is the owner of the table.  If the modifyname table does exist, it will alter it with the same structure as the sourcename table.  If the modifyname table exists, it compares the columns and add any that are missing.  The procedure passes an output parameter.  This is used to know if we need to rebuild any view(s) that use the table.  @change = 0 means no changes to structure of modifyname table.  @change=1 means the table was either created or modified and the view(s) need to be rebuilt.  @change = 10 means the sourcename table does not exist.       

CREATE PROCEDURE SP_TableCompare @strUser_nm VARCHAR(10), @sourcename VARCHAR(40), 
	@modifyname VARCHAR(40), @change TINYINT OUTPUT

AS

SET NOCOUNT ON
SET ARITHABORT ON 

--assume no changes at this point.  
SET @change = 0

DECLARE @sourceid INT, @modifyid INT, @userid INT, @field VARCHAR(42)
DECLARE @datatype VARCHAR(40), @length INT, @strsql VARCHAR(8000)
DECLARE @QtrTable VARCHAR(10), @index VARCHAR(8000)

--get the uid for the supplied study
SELECT @userid = uid
FROM sysusers 
WHERE name = @strUser_nm

--check to make sure the sourcename table exists
SET @sourceid = (SELECT so.id FROM sysobjects so, sysusers su
	WHERE su.uid = @userid
	AND su.uid = so.uid
	AND so.name = @sourcename)

--if sourcename table does not exist, we exit the procedure
--with @change = 10.
IF @sourceid IS NULL
BEGIN
	SET @change = 10
	RETURN
END

--get the id of the modifyname table if it exists
SET @modifyid = (SELECT so.id 
	FROM sysobjects so, sysusers su
	WHERE su.uid = @userid
	AND su.uid = so.uid
	AND so.name = @modifyname)

--temp table to see what fields exist in the modifyname table
CREATE TABLE #fields (fieldorder INT, field VARCHAR(42), datatype VARCHAR(20), 
	length INT, tablea BIT, tableb BIT, updated BIT)

--insert all of the fields from the sourcename table
INSERT INTO #fields (fieldorder, field, datatype, length, tablea, tableb, updated)
SELECT colid, sc.name, st.name, sc.length, 1, NULL, 1
FROM syscolumns sc, systypes st
WHERE sc.id = @sourceid
AND sc.xtype = st.xtype
AND sc.name <> 'QtrTable'

--mark the columns that already exist in modifyname table
UPDATE t
SET t.tableb = 1
FROM #fields t, syscolumns sc, systypes st
WHERE sc.id = @modifyid
AND sc.xtype = st.xtype
AND sc.name <> 'QtrTable'
AND sc.name = t.field

--determine if we need to create or modify the modifyname table
IF @modifyid IS NULL
  SET @strsql = 'CREATE TABLE ' + @strUser_nm + '.' + @modifyname + ' ('
ELSE
  SET @strsql = 'ALTER TABLE ' + @struser_nm + '.' + @modifyname + ' ADD '

--if no changes are necessary, exit the procedure.  @change is already = 0
IF (SELECT COUNT(*) FROM #fields WHERE tableb IS NULL) = 0
	RETURN
ELSE
	SET @change = 1

--PRINT @change
--PRINT 'within query'

--Loop through to add the needed fields to the create/alter statement.
WHILE (SELECT COUNT(*) FROM #fields WHERE tableb IS NULL) > 0
BEGIN

	UPDATE #fields SET updated = 0
	
	--get a field to add
	SET @field = (SELECT TOP 1 field FROM #fields WHERE tableb IS NULL ORDER BY fieldorder)
	
	--update the temp table to say the field now exists in the modifyname table
	UPDATE #fields SET tableb = 1 WHERE field = @field
	
	--get the details about the field
	SELECT @datatype = datatype, @length = length FROM #fields WHERE field = @field
	
	--the datatype determines the syntax needed
	IF @datatype IN ('VARCHAR','CHAR')
		SET @strsql = @strsql + @field + ' ' + @datatype + '(' + CONVERT(VARCHAR,@length) + '), '
	ELSE
		SET @strsql = @strsql + @field + ' ' + @datatype + ', '
	
--end the loop to add the needed fields to the create/alter statement.
END

--get rid of the last comma 
SET @strsql = LEFT(@strsql,(LEN(@strsql)-1))

--close the parenthesis for the create statement
IF LEFT(@strsql,6) = 'CREATE'
	SET @strsql = @strsql + ')'

--PRINT @strsql
--create/alter the table
EXEC (@strsql)

Rate

Share

Share

Rate