Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

List all SQL View references in a SQLServer 2008R2 server

Posted on 2013-01-04
3
514 Views
Last Modified: 2013-01-10
Hi Guys,

IS there a way i can figure out where a specific view ("vwSample") sql view is referenced in a SqlServer 2008r2 server.

Thanks
0
Comment
Question by:kishan66
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 200 total points
ID: 38745174
Knock yourself out..

/*
sys.dm_sql_referencing_entities
	Returns one row for each entity in the current database that references another user-defined entity by name
	http://msdn.microsoft.com/en-us/library/bb630351.aspx

sys.dm_sql_referenced_entities
	Returns one row for each user-defined entity referenced by name in the definition of the specified referencing entity. 
	http://msdn.microsoft.com/en-us/library/bb677185.aspx
	
	QUESTION:  What objects ARE [referenced by | referencing] X?
*/

-- The following example returns the entities (tables and columns) that are referenced by the database-level DDL trigger ddlDatabaseTriggerLog.

USE AdventureWorks2008R2;
GO

/*
Quesiton:  What objects are referenced by X?
           What tables, views, functions, etc.  does my really long SP reference? 
	SELECT blah, blah, balh
	FROM sys.dm_sql_referenced_entities ('My Object Name', 'OBJECT');
*/

SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name, referenced_minor_id, referenced_class_desc
FROM sys.dm_sql_referenced_entities ('ddlDatabaseTriggerLog', 'DATABASE_DDL_TRIGGER');
GO

-- The following example returns the entities that are referenced by the user-defined function dbo.ufnGetContactInformation.
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name, referenced_minor_id, referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('dbo.ufnGetContactInformation', 'OBJECT');
GO

/*
Question:  What objects are referencing X?
           What functions, stored procs, check constraints, etc. call my table? 
*/

SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('Production.Product', 'OBJECT');
GO

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 38748802
For a different approach download Red-Gate's add-in SQL Search to find all the instances of the VIEW in the same database or all databases.
0
 
LVL 12

Assisted Solution

by:sachitjain
sachitjain earned 100 total points
ID: 38751201
Right click the view and click View dependencies in SQL Server Management Studio's object explorer. This would provide you 2 options:
Objects that depend on [ViewName]
Objects on which [Viewname] depends
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question