If dbcc upgrade_object reports
the existence of select * in a view,
compare the output of syscolumns for the original
view to the output of the table, to determine whether columns have
been added to or deleted from the table since the view was created.
-
For example, suppose you have the following statement:
create view all_emps as select * from employees
-
Before upgrading the all_emps view,
use the following queries to determine the number of columns in
the original view and the number of columns in the updated table:
select name from syscolumns
where id = object_id("all_emps")
select name from syscolumns
where id = object_id("employees")
-
Compare the output of the two queries.
-
If the table contains
more columns than the view, and retaining the pre-upgrade
results of the select * statement is important,
change the select * statement to a select statement
with specific column names.
-
If the view was created from multiple
tables, check the columns in all tables that comprise the view and
rewrite the select statement if necessary.
Warning! Do not execute a select * statement
from the view. Doing so upgrades the view and overwrites the information
about the original column information in syscolumns.
-
Another way to determine the difference between the columns
in the view and in the new tables is to run sp_help on
both the view and the tables that comprise the view.
-
This comparison works only for views, not for other compiled
objects. To determine whether select * statements
in other compiled objects need to be revised, review the source
text of each compiled object.