Home >Database >Mysql Tutorial >比较两个数据库中的视图/存储过程的结构(结构比较,不是功能比较)
比较两个数据库中的视图/存储过程的结构(结构比较,不是功能比较)
- WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal
- 2016-06-07 16:03:491003browse
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('
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 ''库 ['+@DB2+'] 中无''
ELSE ''结构不同'' END
,对象名称=ISNULL(A.NAME,B.NAME)
FROM(
SELECT A.NAME,A.XTYPE,B.COLID,B.TEXT
FROM ['+@DB1+']..SYSOBJECTS A,['+@DB1+']..SYSCOMMENTS B
WHERE A.ID=B.ID AND A.XTYPE IN(''V'',''P'') AND A.STATUS>=0
)A FULL JOIN(
SELECT A.NAME,A.XTYPE,B.COLID,B.TEXT
FROM ['+@DB2+']..SYSOBJECTS A,['+@DB2+']..SYSCOMMENTS B
WHERE A.ID=B.ID AND A.XTYPE IN(''V'',''P'') 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,'''')<>ISNULL(B.TEXT,'''')
GROUP BY A.NAME,B.NAME,A.XTYPE,B.XTYPE
ORDER BY 类型,匹配情况,对象名称')
GO
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：数据库基础再总结Next article：数据更新、视图的创建与修改