October 1, 2010 at 2:43 pm
I have read [highlight=#ffff11]http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/e7e8b408-1bf5-41db-b679-5cfda0b6d30c[/highlight] and i understand change the structure of a table will not replicate the change in the view. But i am just wondering, i am pretty sure we have tons of views of which many tables are changed but we never had to rebuild the views. IS there any check point sql does by itself that is fixing this or am i wrong?
October 1, 2010 at 2:58 pm
It is best to manually do this after changing the underlying tables.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 2, 2010 at 5:26 am
It depends on how your views are put together. If they're all simply "SELECT *" type of views, then they more or less fix themselves as they are referenced. On the other hand, if you have a table where you've dropped a column or changed a column name and that column is referenced by a view by name, "SELECT MyColumn", that view needs to be updated, by you, to reflect the change.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 2, 2010 at 8:59 am
Grant Fritchey (10/2/2010)
It depends on how your views are put together. If they're all simply "SELECT *" type of views, then they more or less fix themselves as they are referenced. On the other hand, if you have a table where you've dropped a column or changed a column name and that column is referenced by a view by name, "SELECT MyColumn", that view needs to be updated, by you, to reflect the change.
The down side of the "select *" type of views with sqlserver is that they don't just refresh if they have been referenced since startup.
Best is to use "sp_refreshview" so you are sure the refresh is executed !
Simplest way:
Declare @sql as NVarchar(max)
Set @sql = ''
Select @sql = @sql
+ 'EXEC sp_refreshview ''['+TABLE_SCHEMA+'].['+TABLE_NAME + ']'';
' From INFORMATION_SCHEMA.VIEWS
-- print @sql
Exec (@sql)
Keep in mind, this will also show invalid views that exist in your db.
(i.e. non "select *"-views)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 2, 2010 at 9:12 am
ALZDBA (10/2/2010)
Grant Fritchey (10/2/2010)
It depends on how your views are put together. If they're all simply "SELECT *" type of views, then they more or less fix themselves as they are referenced. On the other hand, if you have a table where you've dropped a column or changed a column name and that column is referenced by a view by name, "SELECT MyColumn", that view needs to be updated, by you, to reflect the change.The down side of the "select *" type of views with sqlserver is that they don't just refresh if they have been referenced since startup.
Best is to use "sp_refreshview" so you are sure the refresh is executed !
Simplest way:
Declare @sql as NVarchar(max)
Set @sql = ''
Select @sql = @sql
+ 'EXEC sp_refreshview ''['+TABLE_SCHEMA+'].['+TABLE_NAME + ']'';
' From INFORMATION_SCHEMA.VIEWS
-- print @sql
Exec (@sql)
Keep in mind, this will also show invalid views that exist in your db.
(i.e. non "select *"-views)
Thanks for that tidbit - useful info and script.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply