sql command to list references to table or view? 2

[jenlion]

Is there a SQL command that will provide a list of views that reference a given table or view?

Here's my problem: I added a column to a view in a production database. Normally I don't like to do this, but it's an eensy-weensy change, I've done it before, and they really needed it. And it's just a view.

Well, it turns out there's ANOTHER view that references this view. It does a union of this view with another view that had the same column list.

Once I added a column, the two views were out of whack (they were both "select *"). Caused a major problem.

Good news is I solved major problem. Bad news is major problem was my own d*** fault.

Is there some sp_help something that I can run to check for this before I screw something else up next time?

 

[Geoka]

You may want to look at INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

 

[jenlion]

BINGO! Thanks.

 

[gmmastros]

I would also encourage you to take a look in Books On Line for [!]sp_refreshview[/!]

http://msdn.microsoft.com/en-us/library/ms187821.aspx

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Run this is a query window:

SELECT 'EXEC sp_refreshview ''' + Table_Name + '''' 
FROM   Information_Schema.Tables 
Where  Table_Type = 'View'

This will generate a row for all views in your database. Then, copy/paste the output in to a new query window and run the code. Presto-chango.... all of your views will be refreshed.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jenlion]

Sweet! I had no idea. Very cool. Thanks!