Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.
----Option 1
SELECTÂ DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECTÂ DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
Reference : Pinal Dave (http://www.SQLAuthority.com)
167 Comments. Leave new
Hello
Is there a way to get a list of which stored procedures are called by triggers in the database?
Try this
Exec sp_depends trigger_name
thanks!
Hello,
I am looking for the following information, can some one help.
Looking for help, I am looking for SQL script which will provide me the
• store procedure name
• Schedule of this procedure
• Last Run time
• Run by
• Table Name/Database against that procedure is running.
thanks, very good solution .
hi
how i find that a particular query is used in how many store procedures
thanx
This is a very nice article…. thanks for sharing.
hi pinal i m ur fan….for dot net N ms sql ser
i m having problem how can return two table in stored procedure in mysql
and how can hold those two table in java seprately IN JAVA
You can use two SELECT statements to return two resultsets. The application JAVA will be able to catche each of them using NEXTRECORDSET property
Hi
It is very Helpfull
Hi
does stored procedure execute only one select query
create procedure proc_purchase_det(@p_id int)
AS
Begin
select SUM(TotalCost)as ‘Month Total’ from PurchaseItemDetails where
TotalCost > 25000
select * from PurchaseItemDetails where purchaseid = @p_id
END
i m getting error for this SP
please clarify me
Thanks it is working good…
create procedure proc_purchase_det(@p_id int)
AS
Begin
select SUM(TotalCost)as [Month Total] from PurchaseItemDetails
where TotalCost > 25000
select * from PurchaseItemDetails where purchaseid = @p_id
END
Great work! Thanks alot :)
Good Clean Info Thanks
thanks a lot !!
Hello,
I wan to get particular record from database.
suppose i have 1000 rows and i want to get record from 75 to 100 than what i have to do…
is there any way to add With Encrypt in every all ready created stored procedures….
To my knowledge there is no built in function in mssql. A script could do this, but it would be too extensive to paste and explain it in a comment like this. Maybe Pinal can write an article and show us an example.
Before you go further with encryption, I would like to give you our reason why we DEcrypted our sprocs.
First the good sides of encryption:
* you want to hide what the sprocs are doing
* you want to minimize the risk of someone changing sprocs on the fly
Now to the not so good sides:
* It gives you a false impression of security. We had encrypted sprocs, developed by a former employee, that we couldn’t understand what they were doing so we decrypted them (yes, you can decrypt encrypted sprocs)
* you can’t see what is going on inside a sproc because the code is hidden!
Read the the bad sides again, especially the last one. Weigh the (partly false) advantages of encryption and the disadvantage and soon you end up understanding it is better to restrict databases access and implement a professional version handling process of sprocs so you are able to “roll back” to the version that once was accepted, before that creative developer did her/his “on the fly”-hack which messed up everything.
thanks for reply jens……
Great work :) Thanks alot :)
heyy, nice post. thanks a lot.
but i’m looking for the oracle version. can you help me ??
Your script returns wrong results. Did you realize that?