list of views referencing a table

Question

Is there any way to know if a particular table is being referenced by any Views or not.I used the below code which gives only SP's and function names:

select * from sys.objects p inner join sys.sql_modules m
on p.object_id = m.object_id 
where m.definition like '%abc%'

Please help!!!

Answer 1

select * 
from INFORMATION_SCHEMA.VIEWS 
where VIEW_DEFINITION like '%abc%'

Answer 2

First, your query gives views in the result set (I tried it on AdentureWorks2012 -> Production.Product table):

If you're using SQL Server 2008 or above, you can use the sys.sql_expression_dependencies catalog view. For example:

SELECT 
referencing_object_name = o.name, 
referencing_object_type_desc = o.type_desc, 
referenced_object_name = referenced_entity_name, 
referenced_object_type_desc = o1.type_desc 
FROM sys.sql_expression_dependencies sed 
INNER JOIN sys.objects o 
ON sed.referencing_id = o.[object_id] 
LEFT OUTER JOIN sys.objects o1 
ON sed.referenced_id = o1.[object_id] 
WHERE referenced_entity_name = 'YourTable'

It will give you nice look on each by-name dependency on a user-defined entity

For column level dependencies you can use the sys.dm_sql_referenced_entities function

Hope this helps

Answer 3

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

If that doesn't fit your bill - you could also check out the sysdepends catalog view in SQL Server - it lists what objects depend on what (see details in the MSDN docs).

To find out what objects depend on a given table, you could use something like:

SELECT 
 id,
 OBJECT_NAME(ID)
FROM sys.sysdepends
WHERE depid = OBJECT_ID('YourTable')

That should give you a list of all objects depending on that table (or view or whatever you're checking)