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 output
AS
BEGIN
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
end
end
我想 写个 存储过程 或是 方法来实现,
(查看存储过程中都使用了哪些表,调用了哪些存储过程。)比如 像上面的存储过程p_addUser,
使用了表 t_UserInfo,t_Relation_User_Group
调用了存储过程 sp_xxx 请高手赐教~!!
@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 output
AS
BEGIN
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
end
end
我想 写个 存储过程 或是 方法来实现,
(查看存储过程中都使用了哪些表,调用了哪些存储过程。)比如 像上面的存储过程p_addUser,
使用了表 t_UserInfo,t_Relation_User_Group
调用了存储过程 sp_xxx 请高手赐教~!!
--SqlServer数据库字典--主键.外键.约束.视图.函数.存储过程.触发器.sqlSELECT DISTINCT TOP 100 PERCENT o.xtype, CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN 'PK' THEN '主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图' WHEN 'FN' THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值' ELSE '存储过程' END AS 类型, o.name AS 对象名, o.crdate AS 创建时间, o.refdate AS 更改时间, c.text AS 声明语句 FROM dbo.sysobjects o LEFT OUTER JOIN dbo.syscomments c ON o.id = c.id WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND (OBJECTPROPERTY(o.id, N'IsMSShipped') = 0) ORDER BY CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN 'PK' THEN '主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图' WHEN 'FN' THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值' ELSE '存储过程' END DESC
@objectname nvarchar(200)
ASSET NOCOUNT ON
SET @objectname = '%' + @objectname + '%'DECLARE @SQL nvarchar(max)
SET @SQL = 'SELECT DISTINCT OBJECT_NAME(object_id) FROM SYS.SQL_MODULES WHERE definition LIKE @objectname'EXEC sp_executesql @SQL,N'@objectname nvarchar(200)',@objectnameSET NOCOUNT OFF--测试ListRelatedUSPs 'tbl_Class'ListRelatedUSPs 'usp_Data_AddClass'
噢,没看清楚.试试如下的查询.
--查询
SELECT
sproc_name = CASE WHEN RANK_1 > 1 THEN '' ELSE sproc_name END
,table_name = CASE WHEN RANK_2 > 1 THEN '' ELSE table_name END
,column_name
FROM
(
select sp.name as sproc_name
,t.name as table_name
,c.name as column_name
,RANK_1 = ROW_NUMBER()OVER(ORDER BY (SELECT 1 ) )
,RANK_2 = ROW_NUMBER()OVER(PARTITION BY t.name ORDER BY sp.name )
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
join sys.columns c
on c.object_id = t.object_id
and c.column_id = d.referenced_minor_id
where sp.type = 'P'
AND sp.name = 'usp_GetTypeByShenID')B---结果()
sproc_name table_name column_name
usp_GetTypeByShenID tbl_ShenApplicationTemplate ShenApplicationTypeID
usp_GetTypeByShenID tbl_ShenApplicationTemplate ShenTemplateID
usp_GetTypeByShenID tbl_ShenApplicationType ShenApplicationTypeID
usp_GetTypeByShenID tbl_ShenApplicationType ShenApplicationTypeName
usp_GetTypeByShenID tbl_ShenApplicationType IsCompanySelected
name type updated selected column
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- ------- -------- --------------------------------------------------------------------------------------------------------------------------------
dbo.tc user table no yes KHDM
dbo.tc user table no yes JE
在当前数据库中,指定的对象引用了以下内容:
name type updated selected column
-------- ------------------ -------- ---------- --------
dbo.tc user table no yes KHDM
dbo.tc user table no yes JE
*/
朋友 ,你的方法 执行下来,只能显示 部分的一些表的信息,不能够把 所有的给显示出来。 使用Sp_depends 只会 显示 与这个表 有关联的 表。
至于 如何 显示出 该存储过程 中 使用到了 哪些 存储过程 还没有解决,
我还想再在问题附加一个 ,如何将存储过程中使用到的(方法 )也 给显示出来。
如下的SQL代码 可以做到查找一个存储过程调用的所有存储过程.当然你会发现它的效率极低.
特别是一个库里的存储过程总数很多时.所以如下的代码仅供参考.其中一些内容注释了,
功能也仅仅是查找存储过程和调用存储过程的关系.--代码(存储过程)DROP PROCEDURE Find_RelatedTabUsp_INUsp
GO
CREATE PROCEDURE Find_RelatedTabUsp_Inusp
@Find_Usp varchar(200)
ASSET NoCOUNT ON
DECLARE @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 #ContentUsp
CREATE TABLE #ContentUsp (usp_name varchar(200),Reprinted_name varchar(200))DECLARE mycursor CURSOR FOR
SELECT NAME FROM sys.procedures
OPEN mycursor
FETCH NEXT FROM mycursor INTO @usp_name
WHILE @@FETCH_STATUS=0
BEGIN
-- PRINT @usp_name
DECLARE @object_name varchar(MAX)
SET @object_name = @usp_name
DECLARE @SQL nvarchar(max)
SET @SQL = 'INSERT INTO #ContentUsp
SELECT 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_name
END
CLOSE mycursor
DEALLOCATE 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_shenClassCluster
usp_shenData_All usp_shenModelClass
usp_shenData_All usp_shenJob
usp_shenData_All usp_shenClassGroup
usp_shenData_All usp_shenClassCoachingTip
usp_shenData_All usp_shenClassType
usp_shenData_All usp_shenClassGoalStmt
usp_shenData_All usp_shenClassIntrvQstn
usp_shenData_All usp_shenLearningRef
usp_shenData_All usp_shenEmployee
usp_shenData_All usp_shenIndustry
usp_shenData_All usp_shenJobFocus
usp_shenData_All usp_shenUser
usp_shenData_All usp_shenJobFunction
usp_shenData_All usp_shenClass
usp_shenData_All usp_shenLearningRefType
usp_shenData_All usp_shenModel
usp_shenData_All usp_shenLocation
usp_shenData_All usp_shenOrgUnit
usp_shenData_All usp_shenPosition
usp_shenData_All usp_shenClassProficiencyBI
usp_shenData_All usp_shenClientCompany
usp_shenData_All usp_shenClassClass
Change
SELECT * FROM #ContentUsp WHERE usp_name = 'usp_Data_ValidateData_All'TO
SELECT * FROM #ContentUsp WHERE usp_name = @Find_Usp
sys.dm_sql_referenced_entities
sys.dm_sql_referencing_entities
SQL 2008 sys.sp_Depends还是很强大的哇,试试
sp_depends 只能 显示 部分的,不能 很好的显示,出来。
如果你的存储过程有如下格式,且命名为usp.txtCREATE PROCEDURE usp_Forfirst
AS
BEGIN EXEC usp_ForTwo
PRINT 'Ok1'
EXEC usp_ForThreePRINT 'Ok2'
EXEC usp_ForFour
PRINT 'Ok3'END---保存此存储过程的内容.
执行如下命令:grep usp usp.txt|sed '/--/d'|sed '/CREATE PROCEDURE/d'|sed 's/EXEC //g'|sed 's/\t\t//g' >temp.txt结果为:
usp_ForTwo
usp_ForThree
usp_ForFour
博客分类: SQL SERVER
SQL ServerSQL
方法一:Sql代码
select *
from sysobjects o, syscomments s
where o.id = s.id
and text like '%yyao%'
and o.xtype = 'P' select *
from sysobjects o, syscomments s
where o.id = s.id
and text like '%yyao%'
and o.xtype = 'P' 将yyao替换成自己要查找的文本 方法二:Sql代码
select routine_name,routine_definition,routine_type
from information_schema.routines
where routine_definition like '%Parent%'
order by routine_type select routine_name,routine_definition,routine_type
from information_schema.routines
where routine_definition like '%Parent%'
order by routine_type 将Parent替换成自己要查找的文本 方法三:Sql代码
sp_depends customer sp_depends customer 此方法只能查找数据库对象,如表、视图、存储过程、函数