sp_columns

Mark Bycroft

unread,

Mar 6, 2000, 3:00:00 AM3/6/00

to

Hi all,

I'm getting some unusual behaviour with the system stored procedure
sp_columns in SQL Anywhere 5.5.05. It returns the columns descriptions
for a table but it is not returning them in the col id order as I expect
it to. (It does in MS SQL 6.5). Has anyone else noticed this or is it
just me? Or better still does anyone have a workaround which will allow
me to return the columns in colid order as my customers are beginning to
moan again ... :-(

Thanks

Mark

Jason Hinsperger

unread,

Mar 6, 2000, 3:00:00 AM3/6/00

to

You can look at the sp_columns() stored procedure and add an order by
clause to the result set if you want to get the results in a specific
order.

Jason Hinsperger
Product Quality
Adaptive Server Anywhere

Mark Bycroft

unread,

Mar 6, 2000, 3:00:00 AM3/6/00

to

Nice idea but I don't seem to have access to it. I've looked everywhere and
can't seem to be able to get to it anywhere. Can you give me directions
please!

Mark

Kathleen Beaumont

unread,

Mar 6, 2000, 3:00:00 AM3/6/00

to

Mark Bycroft wrote:
>
> Nice idea but I don't seem to have access to it. I've looked everywhere and
> can't seem to be able to get to it anywhere. Can you give me directions
> please!

In Sybase Central, Use Tools | Connection Profiles and create a profile
for your database server.

Open the little icon that represents your db server, and locate your
database. Right mouse click on the cylinder for your db and choose
Filter Objects. Place checkmarks in the users/groups called dbo and SYS.
Press OK.

Expand the listing under the cylinder, and open the folder called
Procedures & Functions. In there you will find all of your procs, dbo
procs and system procs. Including sp_columns. Double-click on sp_columns
and your ORDER BY statement. Save the change. Return to ISQL and see if
it does what you want it to do.

HTH,
Kathleen

Mark Bycroft

unread,

Mar 7, 2000, 3:00:00 AM3/7/00

to

Kathleen,

Thanks for that it works fine, it seems a bit strange that the results from
sp_columns are not ordered. I looked at MS SQL servers sp_columns and that does
have the order by in it. My next problem now though is how do I reflect this
change in all of my customers databases? Most of them do not have the knowledge
or the inclination to do what you suggested, is there any way I can do this
programmatically through SQL so that I can just send them a script to run?

Regards

Mark

Mark Bycroft

unread,

Mar 7, 2000, 3:00:00 AM3/7/00

to

Got it, all I need to do is send out a script with the alter procedure SQL.

Thanks to all

Mark

Kathleen Beaumont

unread,

Mar 7, 2000, 3:00:00 AM3/7/00

to

Mark Bycroft wrote:
> My next problem now though is how do I reflect this
> change in all of my customers databases? Most of them do not have the knowledge
> or the inclination to do what you suggested, is there any way I can do this
> programmatically through SQL so that I can just send them a script to run?

If it were me, I would copy the contents of sp_columns into a new
procedure and modify that. That way, you'll always know you're working
with our homegrown version, and won't have problems if you upgrade the
database, possibly reinstating the original version of the proc.

Kathleen