This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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
沒有留言:
張貼留言