Determine Table Referenced in a View in SQL Server

list of views referencing a table

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

Determine table referenced in a view in SQL Server

select
  cols.*
from
  sys.sql_expression_dependencies objs
  outer apply sys.dm_sql_referenced_entities ( OBJECT_SCHEMA_NAME(objs.referencing_id) + N'.' + object_name(objs.referencing_id), N'OBJECT' ) as cols
where
  objs.referencing_id = object_id('view_name_here')

Reference: sys.dm_sql_referenced_entities (Transact-SQL)
.

How to find which views are using a certain table in SQL Server (2008)?

This should do it:

SELECT * 
FROM   INFORMATION_SCHEMA.VIEWS 
WHERE  VIEW_DEFINITION like '%YourTableName%'

Determine Which Objects Reference a Table in SQL Server

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??

Finding a referenced table in multiple views using SQL

In the below query, instead of the matchingstring replace your tablename, it will returns list of objects related to the search string

SELECT DISTINCT SO.[name]
FROM sysobjects SO 
JOIN syscomments SC ON SC.Id = SO.Id 
WHERE SC.[text] LIKE '%matchingstring%'

Search a group of SQL Server views for tables use

You can select this from INFORMATION_SCHEMA.VIEWS:

SELECT * 
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%Test%';

MS SQL view

Find all references to a table column in SQL Server 2008

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??

Where is the definition of the view stored in SQL Server

TL;DR;

When you create a view, SQL-server will store the view information in three separate sys tables: sys.views, sys.columns, and sys.sql_expression_dependencies.

When you alter the underlying table design, the view doesn't automatically get updated. You can use one of the three methods to update the View information:

• Drop/Create the View.
• sp_refreshview 'ViewName'
• sp_refreshsqlmodule 'ViewName'

---

Looking at Microsoft Documentation, you can see that:

If a view depends on a table or view that was dropped, the Database Engine produces an error message when anyone tries to use the view. If a new table or view is created and the table structure does not change from the previous base table to replace the one dropped, the view again becomes usable. If the new table or view structure changes, the view must be dropped and re-created.

If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies. The text of the CREATE VIEW statement is stored in the sys.sql_modules catalog view.

Now to answer your questions:

How does SQL Server store this compilation?

It stores them in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.

Does it a keep a definition of all compiled objects somewhere in one of the system databases?

Yes, it does. This is why you don't get the updated table design unless you drop/create your view again or by just running: sp_refreshview 'ViewName'.

After saying this, following your example. I created the table and the view. Here's the information from the catalog views:

select v.name,
       v.object_id,
       v.type_desc,
       col.name,
       col.column_id,
       sed.referenced_class_desc,
       sed.referenced_entity_name
FROM sys.views as v
inner join sys.columns as col
    on col.object_id = v.object_id
inner join sys.sql_expression_dependencies as sed
    on sed.referencing_id = v.object_id
where v.name = 'vwTest'

We get the next result:

name      object_id   type_desc   name           column_id   referenced_class_desc   referenced_entity_name
--------- ----------- ----------- -------------- ----------- ----------------------- -----------------------
vwTest    581577110   VIEW        ID             1           OBJECT_OR_COLUMN        Test

You can however refresh the view using sp_refreshview 'ViewName', after executing it, the above query will return:

name      object_id   type_desc   name           column_id   referenced_class_desc   referenced_entity_name
--------- ----------- ----------- -------------- ----------- ----------------------- -----------------------
vwTest    581577110   VIEW        ID             1           OBJECT_OR_COLUMN        Test
vwTest    581577110   VIEW        SomethingElse  2           OBJECT_OR_COLUMN        Test

As mentioned by @Gareth Lyons, sp_refreshsqlmodule 'ViewName' is also an option and will refresh the view details.

---

Related Topics