比较两个数据库中的视图/存储过程的结构(结构比较,不是功能比较)-mysql教程-PHP中文网

比较两个数据库中的视图/存储过程的结构(结构比较,不是功能比较)

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

发布： 2016-06-07 16:03:49

原创

1000 人浏览过

CREATE PROC P_COMPDB@DB1 SYSNAME, --第一个库@DB2 SYSNAME --第二个库ASEXEC(SELECT 类型=CASE ISNULL(A.XTYPE,B.XTYPE) WHEN V THEN 视图 ELSE 存储过程 END ,匹配情况=CASE WHEN A.NAME IS NULL THEN 库 [+@DB1+] 中无 WHEN B.NAME IS NULL THEN 库 [+@D

CREATE PROC P_COMPDB

@DB1 SYSNAME,   --第一个库

@DB2 SYSNAME    --第二个库

AS

EXEC(&#39;

SELECT 类型=CASE ISNULL(A.XTYPE,B.XTYPE) WHEN &#39;&#39;V&#39;&#39; THEN &#39;&#39;视图&#39;&#39; ELSE &#39;&#39;存储过程&#39;&#39; END

    ,匹配情况=CASE 

        WHEN A.NAME IS NULL THEN &#39;&#39;库 [&#39;+@DB1+&#39;] 中无&#39;&#39;

        WHEN B.NAME IS NULL THEN &#39;&#39;库 [&#39;+@DB2+&#39;] 中无&#39;&#39;

        ELSE &#39;&#39;结构不同&#39;&#39; END

    ,对象名称=ISNULL(A.NAME,B.NAME)

FROM(

    SELECT A.NAME,A.XTYPE,B.COLID,B.TEXT

    FROM [&#39;+@DB1+&#39;]..SYSOBJECTS A,[&#39;+@DB1+&#39;]..SYSCOMMENTS B

    WHERE A.ID=B.ID AND A.XTYPE IN(&#39;&#39;V&#39;&#39;,&#39;&#39;P&#39;&#39;) AND A.STATUS>=0

)A FULL JOIN(

    SELECT A.NAME,A.XTYPE,B.COLID,B.TEXT

    FROM [&#39;+@DB2+&#39;]..SYSOBJECTS A,[&#39;+@DB2+&#39;]..SYSCOMMENTS B

    WHERE A.ID=B.ID AND A.XTYPE IN(&#39;&#39;V&#39;&#39;,&#39;&#39;P&#39;&#39;) AND A.STATUS>=0

)B ON A.NAME=B.NAME AND A.XTYPE=B.XTYPE AND A.COLID=B.COLID

WHERE A.NAME IS NULL

    OR B.NAME IS NULL

    OR ISNULL(A.TEXT,&#39;&#39;&#39;&#39;)<>ISNULL(B.TEXT,&#39;&#39;&#39;&#39;)

GROUP BY A.NAME,B.NAME,A.XTYPE,B.XTYPE

ORDER BY 类型,匹配情况,对象名称&#39;)

GO

登录后复制