November 6, 2008 at 1:30 pm
which is best using select * or specify each n every column?
I am very much concrened about the views as and when table structure changes. As you know views will not change when talbe changes we may need to refresh/recreate.
How can we automate this process?
1. if using select* we can just refresh, is there a way to refresh all views in a database at once by using exec sp_refreshview.
2. what can we do if each column is specified, how can we automate drop/create views when table changes.
I am looking for the best solution to automate the process or atleast when a tables is changed i want to do it in single click so that all the views in the database are refreshed or drop/create.
November 6, 2008 at 2:03 pm
There isn't necessarily a great way to do this, although perhaps a tool like SQL Compare (REd Gate) can help.
select * isn't recommended because things can change. It also pulls back more data than needed, but in a view that might not be a problem. If your queries are specifying each column, it might not matter for the view.
November 6, 2008 at 2:13 pm
The standard rule of thumb is not to use select * in a view for the very reason that you would have to refresh the view with every change of the underlying tables and that you might (most probably) are bringing back data that is not necessary every time the view is used.
Ultimately, there are several reasons for using views as follows;
1. To allow users access to some data in a table but not all
2. To bring together data from multiple tables into one "table" for ease of application development, querying, etc.
3. To bring together data from multiple tables into one "table" for performance through the use of indexed (schema bound) views.
These are just a couple of quick ones. So, with that being said, rarely is there a true use for a view that is using select *. I would be interested to hear the business reason that is putting you in this situation.
With that being said, you can automate the sp_refresh view with a simple script that will read sysobjects where xtype = 'v' and build the string so that you are refreshing every view using sp_refreshview. Then use sp_executesql to run the string you created. Again, not the ideal and I would still push back on having my views created that way.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 6, 2008 at 2:32 pm
is there a script where i can find on whole server any views which are using select *. I wud like to know the db and view name.
or
atleast if i can query on a DB to find which views are used with select *, that may work. As i have bunch of db's and servers.
November 6, 2008 at 2:40 pm
You could search through syscomments for "select *" as a key phrase in those objects that are views.
November 6, 2008 at 5:21 pm
Steve Jones - Editor (11/6/2008)
You could search through syscomments for "select *" as a key phrase in those objects that are views.
hoe cud i do tht, can you give me tsql cmd for that pls.
thanks
November 6, 2008 at 6:18 pm
select name
from sys.views
where object_ID in (
select ID
from sys.syscomments
where text like '%select *%')
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 13, 2008 at 11:05 am
can i refresh all the views in a database by using exec sp_refreshview
December 2, 2008 at 9:01 am
Mike Levan (11/13/2008)
can i refresh all the views in a database by using exec sp_refreshview
This is exactly what I usually do. Here is an example:
Declare @sql NVarchar(max)
Set @sql = N''
EXEC sp_RefreshView N''[' +TABLE_SCHEMA+ N'].[' +TABLE_NAME+ N']'''
From INFORMATION_SCHEMA.TABLES
Where TABLE_TYPE = 'VIEW'
Print @sql
EXEC (@sql)
I frequently use "SELECT *" in my views (especially during development) because I don't want to have to modify extra defintions whenever I add a column to the end of a table. Along with using spRefreshView, I have found that this works very well in views as long as you follow these two rules:
1) Only one "*" allowed in a View's output and it must be at the end.
2) Always add new columns at the end, never change the column order.
[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]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply