寻找两个库的同名且有差异的存储过程
CREATE FUNCTION DBO.GET_xxx(@S VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
SET @SQL=''
DECLARE @len int
set @len=LEN(@S)
set @S=REPLACE(@S, char(9), '')
set @S=REPLACE(@S, char(32), '')
set @S=REPLACE(@S, char(10), '')
set @S=REPLACE(@S, char(13), '')
SELECT @SQL=@SQL+ SUBSTRING(@S,NUMBER,1)
FROM MASTER..SPT_VALUES
WHERE TYPE='P' AND NUMBER<@len+1
AND( SUBSTRING(@S,NUMBER,1) LIKE '[A-Z]'-- OR SUBSTRING(@S,NUMBER,1) LIKE '[0-9]'
)
ORDER BY NUMBER
RETURN @SQL
END
go
-- 替换 db1name db2name
select distinct isnull(a.name,b.name) as 对象名称 ,upper(DBO.GET_xxx(a.text)),upper(DBO.GET_xxx(b.text))
from(
select a.name,a.xtype,b.colid,b.text
from [db1name]..sysobjects a,[db1name]..syscomments b
where a.id=b.id and a.xtype in('V','P') and a.status>=0
)a join(
select a.name,a.xtype,b.colid,b.text
from [db2name]..sysobjects a,[db2name]..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 LIKE '%td_planin_inter_qry_h%' and HASHBYTES('MD5',upper(DBO.GET_xxx(a.text))) <>HASHBYTES('MD5',upper(DBO.GET_xxx(b.text)))