sql 已知存储过程 怎么查询哪些功能调用了这些存储过程的

Question

如题所述

Answer 1

sql server查询数据库中所有包含某文本的存储过程、视图和函数的SQL
方法一：
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 此方法只能查找数据库对象，如表、视图、存储过程、函数

Answer 2

使用存储过程
①．定义好存储过程 CREATE PROCEDURE [output_1] @sid int output AS set @sid=2 CREATE PROCEDURE [return_1] (@user_name varchar(40),@password varchar(20)) AS if exists(select id from user_info where user_name=@user_name and password=@password) return 1 else return 0 CREATE PROCEDURE [user_info_1] (@user_name varchar(40),@password varchar(20)) AS select id from user_info where user_name=@user_name and password=@password CREATE PROCEDURE [user_info_2] (@user_name varchar(40),@password varchar(20)) AS SET XACT_ABORT ON BEGIN TRANSACTION delete from user_info where user_name=@user_name and password=@password COMMIT TRANSACTION SET XACT_ABORT OFF CREATE PROCEDURE [user_info_3] AS select * from user_info
②．在Asp中调用 use_proc.asp <!-- #include virtual= "/adovbs.inc " --> <% Set conn=Server.CreateObject( "ADODB.Connection ") conn.Open "course_dsn ", "course_user ", "course_password " '使用recordset调用带两个输入参数和返回纪录集的存储过程 'CREATE PROCEDURE [user_info_1] '(@user_name varchar(40),@password varchar(20)) 'AS 'select id from user_info where user_name=@user_name and password=@password response.write "普通的调用方法： <br/> " set rs=server.createobject( "adodb.recordset ") sql= "user_info_1 ' "&request.querystring( "user_name ")& " ', ' "&request.querystring( "password ")& " ' " rs.open sql,conn,1,1 response.write rs( "id ")& " <br/> " rs.close '使用recordset调用无输入参数，返回纪录集的存储过程，可以使用recordcount等属性 'CREATE PROCEDURE [user_info_3] AS 'select * from user_info response.write " <br/> 返回纪录集，可以使用recordcount等属性： " sql= "exec user_info_3 " rs.open sql,conn,1,1 for i=1 to rs.recordcount response.write " <br/> "&rs( "user_name ") rs.movenext next rs.close set rs=nothing '使用command调用带输出参数的存储过程 'CREATE PROCEDURE [output_1] '@sid int output 'AS 'set @sid=2 response.write " <br/> <br/> 调用带输出参数的存储过程： <br/> " set cmd=server.createobject( "adodb.command ") cmd.activeconnection=conn cmd.commandtext = "output_1 " cmd.parameters.append cmd.createparameter( "@sid ",adinteger,adparamoutput) cmd( "@sid ")=10 cmd.execute() bbb=cmd( "@sid ") response.write bbb& " <br/> " set cmd=nothing '使用command调用带两个输入参数和返回值的存储过程 'CREATE PROCEDURE [return_1] '(@user_name varchar(40)) 'AS 'if exists(select id from user_info where user_name=@user_name) 'return 1 'else 'return 0 response.write " <br/> 调用带两个输入参数和返回值的存储过程： <br/> " set cmd=server.createobject( "adodb.command ") cmd.activeconnection=conn cmd.commandtype = adcmdstoredproc cmd.commandtext = "return_1 " cmd.parameters.append cmd.createparameter( "@return_value ",adinteger,adparamreturnvalue) cmd.parameters.append cmd.createparameter( "@user_name ",advarchar,adparaminput,40) cmd.parameters.append cmd.createparameter( "@password ",advarchar,adparaminput,20) cmd( "@user_name ")= "tuth " cmd( "@password ")= "yyuyu " cmd.execute() rrr=cmd( "@return_value ") response.write rrr set cmd=nothing conn.close set conn=nothing %>

Answer 3

select TEXT from dba_source where type='PROCEDURE' and TEXT like '%存储过程名%' and owner='用户名';

Answer 4

按名称搜索下不就知道了