Compare Table using EXCEPT and INTERSECT

Query to return ALL rows in Table_1 that do not match exactly the rows in Table_2, we can just use Except like this in MS SQL SERVER:


SELECT * FROM Table_1 Except SELECT * FROM Table_2


Query to return ALL rows in Table_2 that do not match exactly in Table_1, we reverse the EXCEPT:


SELECT * FROM Table_2 Except SELECT * FROM Table_1


And query to return ALL rows in Table_1 that match exactly what is in Table_2, we can use Intersect:


SELECT * FROM Table_1 Intersect SELECT * FROM Table_2


In ALL of the above examples, the columns must match between the two tables, of course.
Thus, we can return a listing of ALL rows FROM either table that do not match completely by using UNION ALL to combine the results of both Except queries:


SELECT 'Table_1' AS tblName, * FROM
(SELECT * FROM Table_1 Except 
SELECT * FROM Table_2) x
UNION ALL
SELECT 'Table_2' AS tblName, * FROM
(SELECT * FROM Table_2 
Except SELECT * FROM Table_1) x


And we can now write a very simple stored PROCEDURE that compares any two tables like this:


CREATE PROCEDURE CompareTables @Table_1 VarChar(100), @Table_2 VarChar(100)
AS
DECLARE @sql VarChar(8000)
SET @sql = 'SELECT ''' + @Table_1 + ''' AS tblName, * FROM
(SELECT * FROM ' + @Table_1 + '
Except
SELECT * FROM ' + @Table_2 + ') x
UNION ALL
SELECT ''' + @Table_2 + ''' AS tblName, * FROM
(SELECT * FROM ' + @Table_2 + '
Except 
SELECT * FROM ' + @Table_1 +') x' exec(@sql)


---------------------- 
SELECT Table Column Name FROM Schema. 
  
SELECT COUNT(0) FROM information_schema.columns WHERE Table_Name = 'TableName' 


Compare Two Table Structure in MS SQL Server 
  
Step 1: 
SELECT * INTO #a FROM information_schema.columns a 
WHERE table_name = 'crosswalk_av_ad1'


Step 2:
SELECT * INTO #b
FROM information_schema.columns b -- add linked server name and db AS needed
WHERE table_name = 'TableName'


Step 3:
SELECT * FROM #a a
LEFT JOIN #b b 
ON a.column_name = b.column_name
WHERE a.data_type <> b.data_type