In Adaptive Server version 11.9.3 and later, the results of a select * clause in a stored procedure, trigger, or view that was created in an earlier version of Adaptive Server may be different from what you expect.
For more information about the changes, see the Reference Manual.
If dbcc upgrade_object finds a select * clause in the outermost query block of a stored procedure, it returns an error, and does not upgrade the object.
For example, if a stored procedure has the following statements:
create procedure myproc as select * from employees go
create procedure yourproc as if exists (select * from employees) print "Found one!" go
dbcc upgrade_object returns an error on myproc because myproc includes a statement with a select * clause in the outermost query block. This procedure is not upgraded.
dbcc upgrade_object does not return an error on yourproc because the select * clause occurs in a subquery. This procedure is upgraded.
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 will upgrade the view and the information about the original column information in syscolumns will be overwritten.
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.
