2016年4月19日 星期二

(TSQL)比對兩個資料庫是否有不同(或相同)的Table或是View

DECLARE @DB1Name VARCHAR(MAX) = '資料庫一';
DECLARE @DB2Name VARCHAR(MAX) = '資料庫二';
DECLARE @SQL VARCHAR(MAX);
SET @SQL = '
SELECT TABLE_NAME AS DB1才有的,TABLE_TYPE FROM '+ @DB1Name +'.INFORMATION_SCHEMA.TABLES AS DB1
EXCEPT
SELECT TABLE_NAME,TABLE_TYPE FROM '+ @DB2Name +'.INFORMATION_SCHEMA.TABLES AS DB2
'
EXEC(@SQL);
SET @SQL = '
SELECT TABLE_NAME AS DB2才有的,TABLE_TYPE FROM '+ @DB2Name +'.INFORMATION_SCHEMA.TABLES AS DB2
EXCEPT
SELECT TABLE_NAME,TABLE_TYPE FROM '+ @DB1Name +'.INFORMATION_SCHEMA.TABLES AS DB1
'
EXEC(@SQL);
SET @SQL = '
SELECT TABLE_NAME AS DB1和DB2兩者都有的TABLE或VIEW,TABLE_TYPE FROM '+ @DB2Name +'.INFORMATION_SCHEMA.TABLES AS DB2
INTERSECT
SELECT TABLE_NAME,TABLE_TYPE FROM '+ @DB1Name +'.INFORMATION_SCHEMA.TABLES AS DB1
'
EXEC(@SQL);


執行結果


比對Column改寫成比對Table和View

沒有留言:

張貼留言