Break Views by modifying tables

Question

Post reply

Break Views by modifying tables

Mad Myche

SSCommitted

Points: 1593
More actions
December 26, 2008 at 1:31 pm

#133306

I know that if I change the layout of a table by various methods (moving/adding/deleting columns) that views that are dependant on the table may be broken. I have been told that all I need to do is to open the affected view(s) in Design View and saving the view.
I have taken over quite a few DBs where the previous developer was shortsighted in table design, and I find myself designing and saving views often. What I am looking for is an earier way to do it, like a stored procedure that I can execute to expedite the task
Director of Transmogrification Services

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

From BOL:

USE AdventureWorks;

GO

SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''

FROM sys.objects AS so

INNER JOIN sys.sql_expression_dependencies AS sed

ON so.object_id = sed.referencing_id

WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('Person.Contact');

Check out the topic in BOL under sp_refreshview and you should have all that you need. 🙂

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Lowell SSC Guru Points: 323504 More actions · Answer 2

sp_refresh view will update the metadata of a view...so if you did a view that had select * in it, new columns will be added when refreshed;

here's a cursor that would refresh all the views in a database, for example:

[font="Courier New"]

DECLARE

@viewname VARCHAR(64)

DECLARE c1 CURSOR FOR SELECT name FROM sysobjects WHERE xtype IN ('V')

OPEN c1

FETCH next FROM c1 INTO @viewname

WHILE @@fetch_status <> -1

BEGIN

PRINT 'refreshing ' + @viewname

EXEC sp_refreshview @viewname

FETCH next FROM c1 INTO @viewname

END

CLOSE c1

DEALLOCATE c1[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Heh... everyone gets all bent out of shape when someone uses a cursor in a place where it shouldn't be, but no one says boo when someone uses a cursor correctly. So here it is... someone mark this day down...

Nice job on the cursor usage, Lowell. This is one of the few places that a cursor should ever be used.

With that in mind, let's make the cursor a little less resource hungry and let's make it work if the schema for a view is something besides dbo AND let's make it so it skips any views that have schema binding so we don't get an error...

DECLARE @ViewName SYSNAME

DECLARE c1 CURSOR [font="Arial Black"]FORWARD_ONLY READ_ONLY[/font]

FOR SELECT QUOTENAME(Table_Schema)+'.'+QUOTENAME(Table_Name)

FROM Information_Schema.Views

WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(Table_Schema)+'.'+QUOTENAME(Table_Name)),'IsSchemaBound') = 0

OPEN c1

FETCH NEXT FROM c1 INTO @ViewName

WHILE @@FETCH_STATUS <> -1

BEGIN

PRINT 'Refreshing ' + @ViewName

EXEC dbo.sp_RefreshView @ViewName

FETCH NEXT FROM c1 INTO @ViewName

END

CLOSE c1

DEALLOCATE c1

Works, as is, in both SQL Server 2000 and 2005.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mad Myche SSCommitted Points: 1593 More actions · Answer 4

Thank you Jeff and Lowell, that block of code has been saved and executed (many times).

My SQL in the past has only been writing statements and creating views, with no experience in Stored Procedures and only using Query Analyzer, not writing the scripts themselves

I had found the sample that David had pointed out, but it is 2005/2008 version. 2000 used different table names and descriptors

Director of Transmogrification Services

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Jeff Moden (12/26/2008)
Heh... everyone gets all bent out of shape when someone uses a cursor in a place where it shouldn't be, but no one says boo when someone uses a cursor correctly. So here it is... someone mark this day down...
Nice job on the cursor usage, Lowell. This is one of the few places that a cursor should ever be used.

Heh. Well, this being SQL Server 2000 and no VARCHAR(MAX) available, I'll go along with that. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Heh... Z'actly...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)