How to force View to change automatically when table schema changes?

I am still having problem with making View automatically updates itself when the underlying table schema changes. Running sp_recompile on the view table doesn't seem to work either, as I am still getting old format from the view (in Design mode the view returns the right info, but not when I open the View by doing Open View) even though the underlying schema has changed. Right now I find that I have to go into the View and change it a bit to force a recompilation.

And even if sp_recompile does, it would require that I manually do it each time I change a table. Any idea?Is your view an indexed view?|||Originally posted by sbaru
Is your view an indexed view?

Nope, it's not an indexed view, the view joins a few tables so it's not eligible for that.

Another strange thing is that if I select Open View the results is still from the old view, but if I do Design View the output from running the SQL is correct (not the same as what I saw when I did Open View). It's only when I deliberately change a field or two in the view in Design mode will the Open View gives me the correct output. That's why I am thinking the View is still using the old, compiled execution plan until I changed something in the View. That is a pain though since I am constanly change table schema.|||You should run :

--First option
EXEC sp_refreshview @.ViewName

--Secnod option

CREATE PROCEDURE REFRESH_ALL_VIEWS
AS
DECLARE @.ViewName varchar(100)
DECLARE curViews CURSOR FOR select name from sysobjects where xtype='V'
OPEN curViews
FETCH NEXT FROM curViews INTO @.ViewName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_refreshview @.ViewName
FETCH NEXT FROM curViews INTO @.ViewName
END
CLOSE curViews
DEALLOCATE curViews

GO|||Beautiful! Thank you!

How to find objects by owner

I have an application wich generates database objects (mostly views) within
the application itself. So I have a bunch of views created by a user who I
have to delete but I can't do it because he is owning objects.
Can I write a query to list al the objects owned by this user?
Regardsselect name from sysobjects where uid = user_id('NameOfOwner')
"Zekske" wrote:
> I have an application wich generates database objects (mostly views) within
> the application itself. So I have a bunch of views created by a user who I
> have to delete but I can't do it because he is owning objects.
> Can I write a query to list al the objects owned by this user?
> Regards|||You could use the inbuilt views - just change 'dbo' to your user...
-- tables and views
select * from information_schema.tables
where table_schema = 'dbo'
-- stored procs and functions
select routine_name from information_schema.routines
where routine_schema = 'dbo'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to find objects by owner

I have an application wich generates database objects (mostly views) within
the application itself. So I have a bunch of views created by a user who I
have to delete but I can't do it because he is owning objects.
Can I write a query to list al the objects owned by this user?
Regards
select name from sysobjects where uid = user_id('NameOfOwner')
"Zekske" wrote:

> I have an application wich generates database objects (mostly views) within
> the application itself. So I have a bunch of views created by a user who I
> have to delete but I can't do it because he is owning objects.
> Can I write a query to list al the objects owned by this user?
> Regards
|||You could use the inbuilt views - just change 'dbo' to your user...
-- tables and views
select * from information_schema.tables
where table_schema = 'dbo'
-- stored procs and functions
select routine_name from information_schema.routines
where routine_schema = 'dbo'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to find objects by owner

I have an application wich generates database objects (mostly views) within
the application itself. So I have a bunch of views created by a user who I
have to delete but I can't do it because he is owning objects.
Can I write a query to list al the objects owned by this user?
Regardsselect name from sysobjects where uid = user_id('NameOfOwner')
"Zekske" wrote:

> I have an application wich generates database objects (mostly views) withi
n
> the application itself. So I have a bunch of views created by a user who I
> have to delete but I can't do it because he is owning objects.
> Can I write a query to list al the objects owned by this user?
> Regards|||You could use the inbuilt views - just change 'dbo' to your user...
-- tables and views
select * from information_schema.tables
where table_schema = 'dbo'
-- stored procs and functions
select routine_name from information_schema.routines
where routine_schema = 'dbo'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)