(TSQL)資料表一才有的欄位、資料表二才有的欄位、資料表一和資料表二兩者都有的欄位

	--參考網站
	-- http://stackoverflow.com/questions/13511034/compare-2-different-tables-columns-from-2-different-databases
	-- http://stackoverflow.com/questions/5578911/find-identity-columns-from-another-database
	DECLARE @DB1Name VARCHAR(MAX) = 'DB1';
	DECLARE @DB2Name VARCHAR(MAX) = 'DB2';
	DECLARE @DB1TableName VARCHAR(MAX) = 'TableA';
	DECLARE @DB2TableName VARCHAR(MAX) = 'TableB';
	DECLARE @SQL VARCHAR(MAX)
	--資料表一才有的欄位
	SET @SQL = '
	Select COLUMN_NAME AS 資料表一才有的欄位 From '+ @DB1Name +'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ''dbo'' AND TABLE_NAME = '''+@DB1TableName+'''
	EXCEPT
	Select COLUMN_NAME AS 資料表二 From '+ @DB2Name +'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ''dbo'' AND TABLE_NAME = '''+@DB2TableName+'''
	'
	EXEC(@SQL)
	--資料表二才有的欄位
	SET @SQL = '
	Select COLUMN_NAME AS 資料表二才有的欄位 From '+ @DB2Name +'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ''dbo'' AND TABLE_NAME = '''+@DB2TableName+'''
	EXCEPT
	Select COLUMN_NAME AS 資料表一 From '+ @DB1Name +'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ''dbo'' AND TABLE_NAME = '''+@DB1TableName+'''
	'
	EXEC(@SQL)
	--資料表一和資料表二兩者都有的欄位
	SET @SQL = '
	Select COLUMN_NAME AS 兩邊都有的資料表 From '+ @DB2Name +'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ''dbo'' AND TABLE_NAME = '''+@DB2TableName+'''
	INTERSECT
	Select COLUMN_NAME AS 兩者都有的資料表 From '+ @DB1Name +'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ''dbo'' AND TABLE_NAME = '''+@DB1TableName+'''
	'
	EXEC(@SQL)

view raw 資料表一才有的欄位、資料表二才有的欄位、資料表一和資料表二兩者都有的欄位 hosted with ❤ by GitHub

比對Table或View的版本