April 09, 2009

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.


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

1 comment:

Brad Bowman said...

This how I find them, it is 2 separate queries but I have had good luck with both. Enjoy!

SELECT ROUTINE_NAME, ROUTINE_DEFINITION, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%SEARCHTERM%'
AND ROUTINE_TYPE='PROCEDURE'

SELECT TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%SEARCHTERM%'



Brad Bowman