Database stored procedure

What syntax can be use to know what stored procedure in one database is reading from  a specific table.
FavorableAsked:
Who is Participating?
 
krtyknmsqlConnect With a Mentor Commented:
You can try the below query to find out the stored procedures for the given table.
DECLARE @TblName VARCHAR(50)

SET @TblName = 'Test'

SELECT OBJECT_NAME(A.id) 
FROM dbo.Sysdepends AS A
INNER JOIN SysObjects AS B
ON		A.depid = B.id
WHERE B.name = @TblName

Open in new window

0
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
when I have to find that kind of things, I use the free SQL Search: http://www.red-gate.com/products/sql-development/sql-search/
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LowfatspreadConnect With a Mentor Commented:
if your procedures just use static sql then you can refer to the references and dependencies sections of ssms... however in general it is not possible to know which objects any stored procedure could attempt to access ,since it could be generating code dynamically...

it is also possible for a stored procedure to reference tables etc across multiple instances/databases...

the only source for such information is in your system documentation/specification for the stored procedure code... there are system tables which (can) capture the current sql code being executed from your procedures ,,,

which versions/editions of sql server are you using the system tables/methods differ (widely) between versions ...

there are also techniques for examining and parsing the actual stored procedure source code to determine which objects are referenced, but the success of that depends on a variety of factors as well ... dynamic sql, encryption of source code, styles of coding used,...

not an easy task... do you have  a specific requirement/scenario in mind...?

you probably will also need to consider Triggers, Functions ,SSIS packages and Views  all of which may reside in other databases to the one in which the actual underlying table is situated
0
 
krtyknmsqlConnect With a Mentor Commented:
If I understand it correctly, I believe you are trying to find out tables from the stored procedure? If so, try the below query

DECLARE @Sp VARCHAR(50)

SET @Sp = 'Usp_Test'

SELECT DISTINCT OBJECT_NAME(depid) FROM Sysdepends WHERE id = OBJECT_ID(@Sp)

Open in new window

0
 
FavorableAuthor Commented:
In my case, i have the table, but just trying to know which sp are referencing it.
0
 
Ioannis ParaskevopoulosCommented:
Which sql server do you use?
0
 
Ioannis ParaskevopoulosConnect With a Mentor Commented:
For sql2005 and later you may use:

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%YourTableName%'

Open in new window


Giannis
0
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
your query won't be good for SPs using dynamic SQL
0
 
Ioannis ParaskevopoulosConnect With a Mentor Commented:
I think you are wrong as it searches in the definition of the sps. So the only way of this not working (as i see it) is that the table is passed as a parameter like in the following example:


CREATE PROCEDURE [dbo].[spTest]
	@TableName
AS
BEGIN
         EXEC('SELECT * FROM ' + @TableName)
END

Open in new window


If you run it as

EXEC spTest 'TableA'

Open in new window


TableA is referenced but you won't know it before you execute it. But after all there is no way of knowing what the paameter will be beforehand...

Giannis
0
 
FavorableAuthor Commented:
Excellent contribution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.