Automated updating of all views

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Automated updating of all views

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-03 : 08:15:52

Tom writes "I came upon this subject because, I and a team of codevelopers are working on a new program using a sql database.

Table are being updated every day...
only problem is, there are a lot of views depending on these tables.

Now If you add a field to a table, and you have a view, wich worked before (like select * from table), sql server doesn't update the view (internally he makes it a select field1, field2, ... from table). Now the only way to update this view is by opening it in design mode, executing it, and then saving it.

Now you can image that a complex system has a lot of views, and those views are coupled to different tables, and it isn't allways obvious which views should be renewed for a change in the tables.

So my question is: is there a script/program/setting to update all views? (like a stored procedure that opens, executes and saves the views).

thx in advance"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-03 : 13:07:42

No there isn't. Your views should not be using SELECT * anyway. Explicitly call out the columns in the view regardless of what SQL is doing internally.

Tara

Kristen
Test

22859 Posts

Posted - 2004-06-03 : 13:43:09

I don't know if it gets you to where you want to be <g> but there are System tables in SQL that store the columns used in Table and View definitions. So you could autoMagically generate some ALTER scripts that you could then use to Update the VIEWs.

Quite how you reconcile which columns SHOULD appear in which VIEWs I can't imagine, but. For example, if someone adds 20 TEXT columns to a table do you really want your VIEWs pulling all that data?

Kristen

MAKOO
Starting Member

2 Posts

Posted - 2004-06-10 : 11:14:05

just use these proc
declare @Cursor CURSOR
declare @TableName nvarchar(100)

SET @Cursor = CURSOR FAST_FORWARD
for
select table_name from information_Schema.tables
OPEN @Cursor
FETCH NEXT FROM @Cursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
select 'table name :' + @TableName
exec sp_depends @TableName

FETCH NEXT FROM @Cursor
INTO @TableName
END

CLOSE @Cursor
DEALLOCATE @Cursor

MAKOO
Starting Member

2 Posts

Posted - 2004-06-10 : 11:15:57

sorry my error
declare @Cursor CURSOR
declare @TableName nvarchar(100)

SET @Cursor = CURSOR FAST_FORWARD
for
select table_name from information_Schema.views
OPEN @Cursor
FETCH NEXT FROM @Cursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
exec sp_refreshview @TableName

FETCH NEXT FROM @Cursor
INTO @TableName
END

CLOSE @Cursor
DEALLOCATE @Cursor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources