November 25, 2007 at 10:09 pm
Hi,
I'm very new to 2005, but have used 2000 for a few years.
Previously, I created views as 'Select * From'. Often there were a number of similar views against the same table, as permissions were controlled via these views (update permission on one view, select on another, etc. with no direct end-user access to the tables themselves).
If I needed to make a table change, afterwards, I would simply run a stored procedure, looping through all the views and running 'exec sp_refreshview'. This way, all my views with 'Select * From' would automatically be updated with the table column changes. Any views with specific column names were not affected.
In 2005, when I create a view with 'Select * from', it is automatically updated with all the current column names when I save the view. Then if I add a column to the table and refresh the view, the view is incorrect and all the columns in the view get shifted and aliased.
Is there an easy way around this? Currently, if I modify a table, I must locate all associated views that were originally created with 'Select *', delete all the column names and again enter 'Select *' manually to pick up the table column changes.
Thanks in advance for any assistance.
November 26, 2007 at 12:13 am
This isn't the case what you saying. This is the default but discouraging behavior of the designer.
When you open a view in design mode, though it is a "select *" view, the query pane shows all the columns in the statement and it dazzles when a new column is added to the base table. It simply adds columns as the alias names to columns preceding with the second one, which is so frustrating. But when you open it in edit mode, you could see the exact definition of the view.
--Ramesh
November 26, 2007 at 2:17 am
One way to make this process easier is to automate it.
Books online actually list a script that "could" refresh all the dependent views:
SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects so INNER JOIN sys.sql_dependencies sd
ON so.object_id = sd.object_id
WHERE type = 'V'
AND sd.referenced_major_id = object_id('Person.Contact') -- change the table name
However, this works rather erratically 🙁 sysdepends or sys.sql_dependencies are often completely messed up, especially if you do many alters. You could search through sys.sql_modules (or syscomments on 2000), and identify the dependent views. There is no other good workaround.
This is actually a main reason to avoid select * in the first place. Note that there are several tools available that expand select *-s (usually they determine the columns by parsing the select statements, and then looking up the underlying table columns).
Regards,
Andras
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply