查看数据库对象关系

--查看所有的触发器中是否包含指定文本
select name,text from sysobjects t,syscomments m where type='TR' and m.id=t.id and text like '%T_FY_VARYLISTING%' and t.name in (SELECT name FROM SYSOBJECTS WHERE name LIKE 'TR%')

--查看所有的存储过程中是否包含指定文本
select name,text from sysobjects t,syscomments m where type='P' and m.id=t.id and text like '%T_FY_VARYLISTING%' and t.name in (SELECT name FROM SYSOBJECTS WHERE name LIKE 'P%')


--查看依赖关系
SP_DEPENDS 'P_FY_COUNT_CHANGEPRO'

--查看有没有触发器调用了存储过程P_FY_COUNT_CHANGEPRO
select name,text from sysobjects t,syscomments m where type='TR' and m.id=t.id  and t.name in (SELECT name FROM SYSOBJECTS WHERE name LIKE 'TR%') and text like '%P_FY_COUNT_CHANGEPRO%'

--查看存储过程的文本
SP_HELPTEXT 'P_REPORT_TZ_BGTZ'

 
--相关的表
SELECT * FROM sys.sysdepends
select * from sys.procedures

--查询所有的存储过程的依赖关系
SELECT A.name,C.name FROM sys.procedures as A
    inner join sys.sysdepends as B on A.object_id = B.id
        inner join sys.procedures as C on B.depid=C.object_id
       
    WHERE A.name='P_FY_COUNT'
       
EXEC sp_helptext 'P_FY_COUNT'
EXEC sp_helptext 'P_XT_IDBUILDER'

--查询p_fy_count依赖的存储过程
SELECT name FROM sys.procedures
    where object_id in (SELECT distinct(depid) FROM sys.sysdepends
        where id in (SELECT OBJECT_ID FROM sys.procedures WHERE name='p_fy_count'))

--查询依赖于p_fy_count的存储过程
SELECT name FROM sys.procedures
    where object_id in (SELECT distinct(id) FROM sys.sysdepends
        where depid in (SELECT OBJECT_ID FROM sys.procedures WHERE name='p_fy_count'))
               
/*
P_FY_COUNT    P_XT_IDBUILDER
P_FY_COUNT    P_FY_DELETE_PERIOD
P_FY_COUNT    P_FY_COUNT_CHANGEPRICE
P_FY_COUNT    P_FY_COUNT_DATEWORKER
P_FY_COUNT    P_FY_COUNT_JGTZ
P_FY_COUNT    P_FY_COUNT_PROGRESS
*/

--查看p_fy_count依赖的所有对象
SELECT * FROM SYSOBJECTS WHERE ID IN (SELECT distinct(depid) FROM sys.sysdepends where id in(SELECT OBJECT_ID FROM sys.procedures WHERE name='p_fy_count'))