Table Dependencies

Question

Post reply

Table Dependencies

Sheshadrinath R

SSC Veteran

Points: 268
More actions
December 31, 2005 at 4:14 am

#111126

Please help me in knowing the following things:

1. I know which are all the stored procedures referring a given table?

2. I want to know, which are the tables that is being used by a stored procedure. How can I know the above things?

3. I want to know given a field in a table, which are all the stored procedures using that?

Help me regarding this.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

1 and 2 - sysdepends. It can be out of date, but Red Gate software has a tool to help you determine this.

3 - you can search in syscomments for this, but there isn't an easy way to find it.

k_vasanthkumar Old Hand Points: 349 More actions · Answer 2

sp_depends storedprocedurename

This would give all the dependent objects of the Stored procedure ..

JoeA SSC-Addicted Points: 470 More actions · Answer 3

Be careful with sp_depends. It does not report dependent objects that exist in databases other than your current database - i.e. if you have a cross database query in your proc you won't see the "remote" tables.

JacekO SSCertifiable Points: 6610 More actions · Answer 4

I created few little SPs to help me with some of what you trying to do. They work for me but you may have to modify them to fit your needs.

CREATE PROCEDURE sa_FindObjectDependency

@ObjectName VARCHAR(50)

AS

SELECT DISTINCT name

FROM sysobjects SO

INNER JOIN syscomments SC ON SC.id = SO.id

WHERE ( text LIKE '%' + @ObjectName + '(%'

OR text LIKE '% ' + @ObjectName + ' %'

OR text LIKE '%.' + @ObjectName + CHAR(13) + CHAR(10) + '%'

OR text LIKE '% ' + @ObjectName + ' %'

OR text LIKE '%.' + @ObjectName + CHAR(13) + CHAR(10) + '%')

AND name <> @ObjectName

ORDER BY name

GO

CREATE PROCEDURE sa_FindFieldDependency

@TableName VARCHAR(50),

@FieldName VARCHAR(50)

AS

SELECT DISTINCT name

FROM syscomments SC

INNER JOIN sysobjects SO ON SC.id = SO.id

INNER JOIN syscomments SC1 ON SC.id = SC1.id AND SC1.text LIKE '%' + @TableName + '%'

WHERE SC.text LIKE '%' + @FieldName + '%'

ORDER BY name

The second one is less robust because of the way my tables and fields are named. You may have to use the ORing on the text fields from the first SP in the second one to get this to a level of usability you need.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Sheshadrinath R SSC Veteran Points: 268 More actions · Answer 5

Oh, Thank you very much. This script really works fine. It works good. Thanks for the reply.