How to List the Stored Procedures and Views where your Table is being used

It's quite simple to retrieve the dependencies of your StoredProcedures or Views. Just use the sp_depends stored procedure.

However how do you determine in which Stored Procedure or Views is your table being used. For eg: I wanted to find out which were the Stored Procedures or Views in the Northwind database that used the table 'Employees'.

Here's a quick and dirty way of doing so in SQL Server 2005/2008. It may not be a very reliable query, but it makes your job a lot easier.

SELECT object_name(id) as 'Name', text as 'definition', type_desc

FROM syscomments sc

Inner Join sys.objects so

ON sc.id = so.object_id

WHERE text LIKE '%Employees%'

and so.type IN('P', 'V')

OUTPUT



I mentioned the word 'quick and dirty' since I could not find any other way to solve this requirement. But I am sure there is one! So be nice and share it over here.