Using dbcc upgrade_object – Sybase ADAPTIVE 15.0.2 User Manual

Page 129

CHAPTER 7 Troubleshooting

Installation Guide

113

Determining whether
select * should be
changed in views

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.

Using dbcc upgrade_object

Syntax

dbcc upgrade_object [ ( dbid | dbname

where: