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, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook