Dynamic SQL for Table Comparision

Question

Hello experts,

I have two tables.

Table A has exact same column names as table B.

I need my out put like this.

ID(common for both tables) TableA.col1 TableB.col1 TableA.col2 TableB.col2 ...............for all columns

How can i write it in dynamic sql as i have 100 tables to cmpare.

TIA

Answer 1

This requires SQL 2017. If you are on a lower version, you will need to replace string_agg with the FOR XML PATH kludge, which you may be able to find on your own through Google.

CREATE PROCEDURE compare_tables @tableA nvarchar(260), @tableB nvarchar(260) AS
   SELECT @tableA = quotename(s.name) + '.' + quotename(o.name)
   FROM   sys.objects o
   JOIN   sys.schemas s ON s.schema_id = o.schema_id
   WHERE  o.object_id = object_id(@tableA)

   SELECT @tableB = quotename(s.name) + '.' + quotename(o.name)
   FROM   sys.objects o
   JOIN   sys.schemas s ON s.schema_id = o.schema_id
   WHERE  o.object_id = object_id(@tableB)

   DECLARE @sql nvarchar(MAX)

   SELECT @sql = 'SELECT isnull(A.ID, B.ID) AS ID, ' + 
                         string_agg('A.' + quotename(c.name) + ' AS ' + quotename(c.name + '(A)') + ', ' + 
                                    'B.' + quotename(c.name) + ' AS ' + quotename(c.name + '(B)'), ', ') + '
                  FROM   ' + @tableA + ' AS A 
                  FULL JOIN ' + @tableB + ' AS B ON A.ID = B.ID
                  ORDER BY ID'
   FROM  sys.columns c
   WHERE object_id = object_id(@tableA)
     AND c.name <> 'ID'

   PRINT @sql
   EXEC(@sql)
go
CREATE TABLE links (ID int NOT NULL, a varchar(20), d date, f float)
CREATE TABLE rechts (ID int NOT NULL, a varchar(20), d date, f float)
go
INSERT links(ID, a, d, f) VALUES(1, 'Albin', '1876-12-12', pi()), (3, 'Tratt', '1964-11-07', 1E34)
INSERT rechts(ID, a, d, f) VALUES(1, 'Albin', '1876-12-12', sin(0.99)), (2, 'Blygsel', '2012-05-17', 0.009)
go
EXEC compare_tables 'links', 'rechts'
go
DROP TABLE links, rechts
DROP PROCEDURE compare_tables

Answer 2

Hi @Induja Rompicherla ，

Thank you so much for posting here in Microsoft Q&A.

Based on Erland's answer, if your SQL Server is 2016 and earlier, you could replace the procedure with below part.

    DECLARE @sql nvarchar(MAX)  
      
    SELECT @sql = 'SELECT isnull(A.ID, B.ID) AS ID, ' +   
					STUFF((SELECT ', '+'A.' + quotename(d.name) + ' AS ' + quotename(d.name + '(A)') + ', ' +   
                                    'B.' + quotename(d.name) + ' AS ' + quotename(d.name + '(B)')  
					FROM SYS.COLUMNS d   
					WHERE c.object_id = d.object_id   
					AND d.name<>'ID'  
					FOR XML PATH('')), 1, 1, ''  
					)				 + '  
                   FROM   ' + @tableA + ' AS A   
                   FULL JOIN ' + @tableB + ' AS B ON A.ID = B.ID  
                   ORDER BY ID'  
    FROM  sys.columns c  
    WHERE object_id = object_id(@tableA)  
      AND c.name <> 'ID'  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table