查看存储过程中都使用了哪些表，调用了哪些存储过程。
ALTER PROCEDURE [dbo].[p_addUser]@UserName varchar(50),@DisplayName varchar(50),@Address varchar(50),@PassWord varchar(50),@Email varchar(50),@Phone varchar(50),@Description varchar(100),@CityId int,@groupId int,@UserID int,@ReturnID int outputASBEGIN select * from T_userinfo where username=@UserName if(@@ROWCOUNT > 0) SELECT @ReturnID=0 else begin INSERT INTO t_UserInfo(UserName,DisplayName,Address,PassWord,Email,Phone,Description,CityId) VALUES(@UserName,@DisplayName,@Address,@PassWord,@Email,@Phone,@Description,@CityId) IF(@@ERROR=0) BEGIN IF(@groupId<>'') BEGIN SELECT @ReturnID = SCOPE_IDENTITY() insert into t_Relation_User_Group(groupId,userId) values (@groupId,@ReturnID) end end ELSE SELECT @ReturnID=-1exec sp_xxx endend
sp_depends 'p_test'在当前数据库中，指定的对象引用了以下内容:name type updated selected column ---------------------------------------------------------------------------------------------------------------- ---------------- ------- -------- -------------------------------------------------------- dbo.tc user table no yes KHDMdbo.tc user table no yes JE
[解决办法]
-- 介绍部分还是用文本文件 加 正则表达式处理的好.sys.sql_dependencies会漏,不是很清楚它的具体实现.如下的SQL代码 可以做到查找一个存储过程调用的所有存储过程.当然你会发现它的效率极低.特别是一个库里的存储过程总数很多时.所以如下的代码仅供参考.其中一些内容注释了,功能也仅仅是查找存储过程和调用存储过程的关系.--代码(存储过程)DROP PROCEDURE Find_RelatedTabUsp_INUspGOCREATE PROCEDURE Find_RelatedTabUsp_Inusp@Find_Usp varchar(200)ASSET NoCOUNT ONDECLARE @usp_name varchar(200)--SET @Find_Usp = 'usp_testIn' --EXEC usp_testOut EXEC usp_testIn if exists (select * from dbo.sysobjects where id = object_id(N'#ContentUsp') and OBJECTPROPERTY(id, N'IsTable') = 1)DROP TABLE #ContentUspCREATE TABLE #ContentUsp (usp_name varchar(200),Reprinted_name varchar(200))DECLARE mycursor CURSOR FORSELECT NAME FROM sys.proceduresOPEN mycursorFETCH NEXT FROM mycursor INTO @usp_nameWHILE @@FETCH_STATUS=0BEGIN-- PRINT @usp_name DECLARE @object_name varchar(MAX) SET @object_name = @usp_name DECLARE @SQL nvarchar(max)SET @SQL = 'INSERT INTO #ContentUspSELECT DISTINCT OBJECT_NAME(object_id),@object_name FROM SYS.SQL_MODULES WHERE definition LIKE ''%''+ @object_name+''%'' AND OBJECT_NAME(object_id) <> @object_name'EXEC sp_executesql @SQL,N'@object_name nvarchar(200)',@object_name FETCH NEXT FROM mycursor INTO @usp_name-- PRINT @usp_nameENDCLOSE mycursorDEALLOCATE mycursor--DECLARE @Call_sql varchar(max)--SELECT @Call_sql= ISNULL(@Call_sql+' &&& ','') + Reprinted_name FROM #ContentUsp WHERE usp_name = @Find_Usp--PRINT @Call_sqlSELECT * FROM #ContentUsp WHERE usp_name = 'usp_Data_ValidateData_All'--SELECT sproc_name = CASE WHEN RANK > 1 THEN '' ELSE sproc_name END--, Obj_name,--Called_uspLists = CASE WHEN RANK > 1 OR Is_Procedure = 1 THEN '' ELSE ISNULL(@Call_sql,'Call Nobody') END--FROM--(--select DISTINCT sp.name as sproc_name-- ,t.name as Obj_name -- ,RANK = RANK()OVER(ORDER BY t.name )-- ,Is_Procedure = OBJECTPROPERTY(d.referenced_major_id,'IsProcedure' )-- ,Called_uspLists = @Call_sql-- from sys.sql_dependencies d -- join sys.objects t -- on t.object_id = d.referenced_major_id -- JOIN SYS.OBJECTS sp -- on sp.object_id = d.object_id --WHERE sp.name = @Find_Usp)BSET NoCOUNT OFF--执行Find_RelatedTabUsp_Inusp 'usp_shenData_All'--测试结果usp_name Reprinted_name usp_shenData_All usp_shenClassClusterusp_shenData_All usp_shenModelClassusp_shenData_All usp_shenJobusp_shenData_All usp_shenClassGroupusp_shenData_All usp_shenClassCoachingTipusp_shenData_All usp_shenClassTypeusp_shenData_All usp_shenClassGoalStmtusp_shenData_All usp_shenClassIntrvQstnusp_shenData_All usp_shenLearningRefusp_shenData_All usp_shenEmployeeusp_shenData_All usp_shenIndustryusp_shenData_All usp_shenJobFocususp_shenData_All usp_shenUserusp_shenData_All usp_shenJobFunctionusp_shenData_All usp_shenClassusp_shenData_All usp_shenLearningRefTypeusp_shenData_All usp_shenModelusp_shenData_All usp_shenLocationusp_shenData_All usp_shenOrgUnitusp_shenData_All usp_shenPositionusp_shenData_All usp_shenClassProficiencyBIusp_shenData_All usp_shenClientCompanyusp_shenData_All usp_shenClassClass
