Solved

System table and Stored Procedure

Posted on 2007-03-26
2
167 Views
Last Modified: 2010-03-19
How can I find the list of  stored procedures which are using a certain table in database?
I have 100 SPs in one database and I am looking for the SPs which are using a table called"Patients".. Is there a system table that has this kind of information in SQL server 2005 so that I don;t have to open up each SP one by one?
0
Comment
Question by:jung1975
  • 2
2 Comments
 
LVL 10

Accepted Solution

by:
ksaul earned 500 total points
ID: 18793511
declare @table varchar(256)
set @table = 'yourtable'

select name
from sysobjects o
inner join syscomments c on o.id = c.id
where c.text like '%' + @table + '%'
and o.type = 'p'
0
 
LVL 10

Assisted Solution

by:ksaul
ksaul earned 500 total points
ID: 18793536
In SQL Management Studio you can also get a list of sps using a table if you right click the table in the Object Explorer and select "View Dependencies"
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Impove long SQL Stored Procedure Performance 14 75
Truncate vs Delete 63 105
SQL query with cast 38 43
Help Required 2 33
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question