Find which views (if any) a field is used in (usin

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!

Answer 1

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.

Answer 2

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

Answer 3

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