继续讨论:如果比较两个数据库表结构是否一致
老宛 2004-08-13 05:46:20 如题:
小弟之前也写了一个,自己留着用的,当然没有邹老大写的好,也贴出来看看,呵呵
DECLARE @DATABASE1 VARCHAR(50),@DATABASE2 VARCHAR(50)
SELECT @DATABASE1='LX',@DATABASE2='YS'
EXEC('SELECT A.[NAME],A.[ID],B.[NAME] AS TABLENAME,A.[XTYPE],C.[NAME] AS TYPENAME,A.[XUSERTYPE],A.[LENGTH],A.[CDEFAULT],A.[DOMAIN],A.[OFFSET],A.[TYPE],A.[USERTYPE],A.[PREC],A.[SCALE]
INTO ##TMP1 FROM '+@DATABASE1+'..syscolumns A JOIN '+@DATABASE1+'..SYSOBJECTS B ON A.[ID]=B.[ID]
JOIN '+@DATABASE1+'..SYSTYPES C ON A.[XTYPE]=C.[XTYPE] WHERE B.XTYPE=''U'' AND B.STATUS>0
ORDER BY B.[NAME]')
EXEC('SELECT A.[NAME],A.[ID],B.[NAME] AS TABLENAME,A.[XTYPE],C.[NAME] AS TYPENAME,A.[XUSERTYPE],A.[LENGTH],A.[CDEFAULT],A.[DOMAIN],A.[OFFSET],A.[TYPE],A.[USERTYPE],A.[PREC],A.[SCALE]
INTO ##TMP2 FROM '+@DATABASE2+'..syscolumns A JOIN '+@DATABASE2+'..SYSOBJECTS B ON A.[ID]=B.[ID]
JOIN '+@DATABASE2+'..SYSTYPES C ON A.[XTYPE]=C.[XTYPE] WHERE B.XTYPE=''U'' AND B.STATUS>0
ORDER BY B.[NAME]')
SELECT '以下是两个数据库相同的部分'
SELECT * FROM ##TMP1 A WHERE EXISTS(SELECT [NAME],TYPENAME,TABLENAME FROM ##TMP2 WHERE [NAME]=A.[NAME]
AND TYPENAME=A.TYPENAME AND TABLENAME=A.TABLENAME)
SELECT '以下是第一个数据库有的第二个数据库没有的或两个都有但不一样的部分'
SELECT * FROM ##TMP1 A WHERE NOT EXISTS(SELECT [NAME],TYPENAME,TABLENAME FROM ##TMP2 WHERE [NAME]=A.[NAME]
AND TYPENAME=A.TYPENAME AND TABLENAME=A.TABLENAME) ORDER BY TABLENAME
SELECT '以下是第二个数据库有的第一个数据库没有的或两个都有但不一样的部分'
SELECT * FROM ##TMP2 A WHERE NOT EXISTS(SELECT [NAME],TYPENAME,TABLENAME FROM ##TMP1 WHERE [NAME]=A.[NAME]
AND TYPENAME=A.TYPENAME AND TABLENAME=A.TABLENAME) ORDER BY TABLENAME
DROP TABLE ##TMP1
DROP TABLE ##TMP2