When a procedural object (procedure, trigger, view, etc.) is created, it's source code is stored in the syscomments table. The default behavior is to expand any use of a "select *" wildcard to the explicit list of columns reflecting the state of the database schema at that time. This is done to avoid unexpected changes to the results sets should ASE have to rebuild the query tree for the object from the sycomments source code following a schema change. The query tree is rebuilt following upgrades, the use of DBCC REBUILDOBJECT, and the use of EXECUTE ... WITH UPGRADE. Without this expansion, columns added to tables could unexpectedly show up in the result sets of procedural objects. This might result in users that have execute permission on the procedure being allowed to see the contents of new columns that they shouldn't be permitted to see and can break the definitions of rpc-based proxy tables based on the procedure.
Example of default behavior:
1> create procedure p1 as select * from sysusers
2> go
1> sp_helptext p1
2> go
# Lines of Text
---------------
1
(1 row affected)
text
-------------------------------------------------------------------------------------
create procedure p1 as/* Adaptive Server has expanded all '*' elements in the
following statement */ select sysusers.suid, sysusers.uid, sysusers.gid,
sysusers.name, sysusers.environ from sysusers
(1 row affected)
(return status = 0)
Example of behavior with traceflag 243.
(Note that traceflag 243 would normally be permanently set on as a -T243 parameter in the RUN_SERVER file)
1> set switch on 243 with override
2> go
Switch 243 is turned on.
All supplied switches are successfully turned on.
1> create procedure p2 as select * from sysusers
2> go
1> sp_helptext p2
2> go
# Lines of Text
---------------
1
(1 row affected)
text
----------------------------------------------------------
----------------
create procedure p2 as select * from sysusers
(1 row affected)
(return status = 0)