Search content within the blog

Friday, April 17, 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

No comments:

Post a Comment