两数据库字段差异比较

DECLARE @SQL VARCHAR(5000)

DECLARE @db1 VARCHAR(100)

DECLARE @db2 VARCHAR(100)

SET @db1 = 'cpho_dev'

SET @db2 = 'cpho_test'

SET @SQL = '

SELECT maintb.TBNAME as '+@db1+'TableName, maintb.name as '+@db1+'colName,

comtb.TBNAME as '+@db2+'TableName, comtb.name as '+@db2+'colName

FROM

(

SELECT S.ID,ST.NAME AS TBNAME,S.[name] FROM '+@db1+'.dbo.syscolumns s

INNER JOIN '+@db1+'.dbo.sysobjects st ON(s.id = st.id AND st.type=''U'')

) AS maintb

LEFT JOIN

(

SELECT S.ID,ST.NAME AS TBNAME,S.[name] FROM '+@db2+'.dbo.syscolumns s

INNER JOIN '+@db2+'.dbo.sysobjects st ON(s.id = st.id AND st.type=''U'')

) AS comtb

ON maintb.TBNAME = comtb.TBNAME AND maintb.name = comtb.name

WHERE comtb.id IS null

ORDER BY maintb.name

EXEC(@SQL);