Find which views (if any) a field is used in (using dependencies?)

Question

I have come across this article detailing how dependencies work and I was wondering if it would be possible to use these to see which views (if any) a field is contained within.

I've come across topics such as this one which show how to find which entities are used within views, but I was hoping to be able to drill down a bit further and instead find out if a field is linked to any views? (Using Sql server 2005)

Thanks!

score 3 · Accepted Answer · answered Nov 22 '10 at 12:24

3

For your question

...find out if a field is linked to any views?

one could use the system view VIEW_COLUMN_USAGE in your database. I created this view

USE [ScratchPad]
GO
CREATE VIEW [dbo].[View_1]
AS
SELECT     second
FROM         dbo.deleteme
GO

Using the query below:

SELECT TOP 1000 [VIEW_CATALOG]
      ,[VIEW_SCHEMA]
      ,[VIEW_NAME]
      ,[TABLE_CATALOG]
      ,[TABLE_SCHEMA]
      ,[TABLE_NAME]
      ,[COLUMN_NAME]
  FROM [ScratchPad].[INFORMATION_SCHEMA].[VIEW_COLUMN_USAGE]

I recieved this result which includes the column and table name

## VIEW_CATALOG VIEW_SCHEMA VIEW_NAME   TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME
ScratchPad       dbo                      View_1     ScratchPad dbo          deleteme   second

Add a WHERE clause to the query and you should get your answer.

If you wish to look at constraints use the view "[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE]"

My system is MSSS 2K8 your 2K5 system should have the same system views

answered Nov 22 '10 at 12:24

RC_Cleland

2,274
14
16

DO you know if there is an easy way to add 2 extra columns onto this script to tell you which field / entity is linked to that in column [table_name] and [column_name]? - rather than having to open up the view and look at it that way? Thanks :) – Dibstar Nov 22 '10 at 13:00
Davin did you scroll over in the second code block? The results include the table name and column name – RC_Cleland Nov 22 '10 at 22:38
I did - sorry if wasn't clear - is it possible to include columns telling you which table / column is used in the related view? (i.e. for column 'second' for table 'deleteme' , what corresponding table and column in view_1 are used - so that I don't have to delve into each view to see how it is linked to the table? – Dibstar Nov 23 '10 at 10:55
I still do not understand your comment, sorry. The view view_1 is a view on one column only named 'second' in the table 'deleteme'. – RC_Cleland Nov 25 '10 at 05:26

Paul Stephenson · Answer 2 · 2010-12-17T15:46:39.803

1

Red Gate Software's SQL Prompt 5 has a Column Dependencies feature that might be useful in this situation. You hover over a column name in a script, and up pops a window containing a list of objects that use that column, including views.

You can download a 14-day trial for free, to see if this feature works for you.

Paul Stephenson
SQL Prompt Project Manager
Red Gate Software

edited Dec 17 '10 at 15:46

answered Nov 22 '10 at 12:00

Paul Stephenson

67,682
9
49
51

score 0 · Answer 3 · answered Nov 22 '10 at 11:48

Have a look at this (free) tool: http://www.atlantis-interactive.co.uk/products/schemasurf/default.aspx

which (quote):

Atlantis Schema Surf is our SQL dependency & live entity ER diagram tool - it is a free animated graphing tool which allows you to easily visualize the dependencies between the objects in your SQL Server databases in a live entity ER diagram.

Awesome tool.

Find which views (if any) a field is used in (using dependencies?)

3 Answers3

Linked