Geeks With Blogs
SharePoint & SQL Thoughts
 
Sometimes there is a need to compare two tables for find out if the schema has changed before implementing data synchronization. There are multiple ways of achiving this requirement. I had a request to provide a process that would compare the tables, figure out which columns have been added, get the datatype and size.
With that I generated the script below, though if there is no need of achiving the datatype and size then information_schema view can be used.
 
DECLARE @source_table sysname
            , @destination_table sysname
            , @colname sysname
            , @datatype sysname
            , @length varchar(100)
            , @i int
            , @n int
            , @sql2 varchar(max)           
DECLARE @SourceTable TABLE
      (name VARCHAR(100), datatype VARCHAR(100), max_length VARCHAR(100), Tablename VARCHAR(100))     
DECLARE @DestTable TABLE
      (name VARCHAR(100), datatype VARCHAR(100), max_length VARCHAR(100), Tablename VARCHAR(100))
DECLARE @DataManup TABLE
      (name VARCHAR(100), datatype VARCHAR(100), max_length VARCHAR(100), Tablename VARCHAR(100), flag bit default 0)     
SET @source_table = '< source table >'
SET @destination_table = '< Destination table >'
SET @i = 0
SET @n = 0
PRINT 'INSERT INTO @SourceTable'
INSERT INTO @SourceTable
SELECT DISTINCT c.name as name , t.name as datatype, t.max_length, 
      u.name as Tablename
      FROM sys.columns AS c
      JOIN sys.types AS t
      ON c.user_type_id=t.user_type_id
      JOIN sys.tables AS u
      ON c.object_id = u.object_id
      WHERE u.name = @source_table
PRINT 'INSERT INTO @DestTable'
INSERT INTO @DestTable
SELECT DISTINCT c.name as name , t.name as datatype, t.max_length, 
      u.name as Tablename
      FROM sys.columns AS c
      JOIN sys.types AS t
      ON c.user_type_id=t.user_type_id
      JOIN sys.tables AS u
      ON c.object_id = u.object_id
      WHERE u.name = @destination_table
PRINT 'Schema differences'
INSERT INTO @DataManup 
      (name, datatype, max_length, Tablename, flag)
      SELECT name, datatype, max_length, Tablename, 0 FROM @SourceTable st WHERE NOT EXISTS
      (SELECT * FROM @DestTable dt WHERE dt.name = st.name)
SET @i = (SELECT COUNT(*) FROM @DataManup )
WHILE @n > @i
      BEGIN
      SET @colname = (SELECT TOP 1 name FROM @DataManup WHERE flag = 0 )
      SET @datatype = (SELECT TOP 1 datatype FROM @DataManup WHERE flag = 0 )
      set @length = (SELECT TOP 1 max_length FROM @DataManup WHERE flag = 0 )
      SET @sql2 = 'ALTER TABLE ' +@destination_table+ ' ADD COLUMN ' + @colname + ' ' + @datatype + ' ('+ @length +')'
      UPDATE TOP (1)@DataManup
      SET flag = 1
      WHERE flag = 0
      PRINT @sql2 
      /*
      To enact the changes
      EXEC (@sql2)
      */
END
Posted on Thursday, January 14, 2010 9:41 AM | Back to top


Comments on this post: Table Schema comparison in SQL Server

# re: Table Schema comparison in SQL Server
Requesting Gravatar...
This will be your friend forever ;o)

http://www.red-gate.com/products/SQL_Compare/index.htm

Left by Gregor Suttie on Jan 14, 2010 9:30 PM

# re: Table Schema comparison in SQL Server
Requesting Gravatar...
Many a times i use to think about the share point when my friends use to talk about it. And then i got to know it better.It was best when i implemented it. check this out. At my time i did not got free. But for you its free for 30days
http://www.sharepointperth.com/30-day-sharepoint-trial
Isn't it good for you. so grape it.

Hope its helpful
Thanks
Hiphop
Left by hiphop on Feb 24, 2010 9:08 PM

Comments have been closed on this topic.
Copyright © Leonard Mwangi | Powered by: GeeksWithBlogs.net