Compare the structure of two tables.

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)