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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

1 Response to "How to List the Stored Procedures and Views where your Table is being used"
  1. Brad Bowman said...
    April 9, 2009 at 6:52 AM

    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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions