Describe INTERSECT and EXCEPT

INTERSECT and EXCEPT

To better understanding please follow this.
Intersect :  It will give you mutual records from Left query and Right query of the Intersect.
Example: 1) A Intersect B or you can follow the below example queries.

Except : It will give you only the left query or Left table result. It means, wouldn't count the mutual records
Example: 1) A Except B -- All the rows from A, which avoids If any same rows from B.


Table A has 50 rows  10 of them are copied from Table B
Table B has 70 rows 20 of them are copied from Table A
                  So Now A INTERSECT B : 10 + 20 = 30
                   
                  So A EXCEPT B : 50 - 30 = 20
                  So B EXCEPT A : 70 - 30 = 40


Small example :

I have database named mydb, consists 5 tables. I got Full backup on 08/10/2010
2 weeks later I had to compare the database with the old backup.
I have installed the database on the same server with different name as mydb_Test.

Note: Of Course If you have the old database available on one of your test servers, which had configured linked server set up .. (No need to install the database again..) Just use the Fully Qualified Object Name as LinkedServerName.DatabaseName.sys.tables
Where,LinkedServerName : should be one of the row from the following query.
            select name as ServerName from sys.servers


Now, My job is to findout the new objects introduced in the current version of
the database as well the dropped objects. Such as Tables, Stored Procs, Triggers, Functions..

Of course, We definitely have couple of other procedures. But I have used this and gotten results fine.

----------------Tables

-- Find new tables

SELECT Name AS NewTables FROM mydb.sys.tables
EXCEPT   -- Will give all the extra tables from mydb.
SELECT Name FROM myDB_Test.sys.tables
ORDER BY name


-- Find Mutual tables

SELECT name AS MutualTables FROM myDB_Test.sys.tables 

INTERSECT
SELECT name  FROM mydb.sys.tables
ORDER BY name

-- Find Dropped tables

SELECT Name AS DroppedTables FROM myDB_Test.sys.tables
EXCEPT   -- Will give all the extra tables from mydb.
SELECT Name AS NewTables FROM mydb.sys.tables
ORDER BY name

This is the same procedure to find stored procedures and Triggers. Just replace tables with procedures and triggers in the code above.

For the functions, Please follow..


SELECT name AS NewFunctionName ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb.sys.objects WHERE type_desc LIKE '%FUNCTION%'
EXCEPT
SELECT name ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb_Test.sys.objects WHERE type_desc LIKE '%FUNCTION%'

SELECT name AS MutualFunctionName ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb.sys.objects WHERE type_desc LIKE '%FUNCTION%'
INTERSECT
SELECT name,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb_Test.sys.objects WHERE type_desc LIKE '%FUNCTION%'

SELECT name AS DroppedFunction ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb_Test.sys.objects WHERE type_desc LIKE '%FUNCTION%'
EXCEPT
SELECT name ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb.sys.objects WHERE type_desc LIKE '%FUNCTION%'