Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

getting useful info from sp_columns

177 views
Skip to first unread message

Bryon Lape

unread,
Sep 30, 1996, 3:00:00 AM9/30/96
to

Is there a sybase command that I am not seeing or can I get sybase to
just give me the structure of a table? That is, its name and the columns
associated with it and their data types. Nothing else. And in a usable form
as in one line of data per row, not 5 lines like what sp_columns spews out.

bryon
--
----------------------------------------------------------------------------
After 200 years, the definitions have reversed.
Bryon Lape - currently living somewhere close to 35 52 44 N, 83 56 15 W
email: bl...@utk.edu WWW:http://aztec.lib.utk.edu/~bryon


Bret Halford

unread,
Oct 1, 1996, 3:00:00 AM10/1/96
to

In article <52p6od$k...@gaia.ns.utk.edu>, bl...@utk.edu (Bryon Lape) writes:
|> Is there a sybase command that I am not seeing or can I get sybase to
|> just give me the structure of a table? That is, its name and the columns
|> associated with it and their data types. Nothing else. And in a usable form
|> as in one line of data per row, not 5 lines like what sp_columns spews out.


Try

select c.name, s.name type from syscolumns c, systypes s where
c.type = s.type and
c.id = object_id("<tablename>")

as a starting point.

---------------------------------------------------------------------
| Bret Halford br...@sybase.com ___|
| Sybase Technical Support fax (303)-486-7831 __|
| 6400 S. Fiddlers Green Circle |__
| Englewood, CO 80111-4954 USA exec sp_realitycheck() |___
| |
#####################################################################


Matthew D. Healy

unread,
Oct 1, 1996, 3:00:00 AM10/1/96
to

In article <52pvmm$r...@fyi.sybase.com>, br...@sybase.com (Bret Halford) wrote:

>
> Try
>
> select c.name, s.name type from syscolumns c, systypes s where
> c.type = s.type and
> c.id = object_id("<tablename>")
>
> as a starting point.

Here's the one I normally use; your terminal should be in 132-col mode
before you launch isql with the -w132 flag.

select
"1_Tab_Col" = o.name + '.' + c.name,
"2_cType" = t.name,
"3_cLength" = c.length
from syscolumns c, systypes t, sysobjects o
where c.id = o.id
and c.usertype *= t.usertype
and o.type in ('U','V')
order by o.name,c.name

What I particularly like about this one is that it gives me the
fully-qualified fieldnames, ready for cutting and pasting into
SQL code that I'm editing.

You might want to write a shell script that pipes this through
isql, then pipes the output through SED or AWK or something to
massage its output format...

---------
Matthe...@yale.edu
http://paella.med.yale.edu/~healy
"Any content-based regulation of the Internet could burn down the
global village to roast the pig." -- Judge Dalzell, on the CDA

Chris Markle

unread,
Oct 1, 1996, 3:00:00 AM10/1/96
to

bl...@utk.edu (Bryon Lape) writes:

> Is there a sybase command that I am not seeing or can I get sybase to
>just give me the structure of a table? That is, its name and the columns
>associated with it and their data types. Nothing else. And in a usable form
>as in one line of data per row, not 5 lines like what sp_columns spews out.

>bryon


>--
>----------------------------------------------------------------------------
>After 200 years, the definitions have reversed.
>Bryon Lape - currently living somewhere close to 35 52 44 N, 83 56 15 W
>email: bl...@utk.edu WWW:http://aztec.lib.utk.edu/~bryon


sp_help <table name>


Phil Allen

unread,
Oct 2, 1996, 3:00:00 AM10/2/96
to

Chris Markle <cma...@abu.bio.uci.edu> wrote in article
<cmarkle....@abu.bio.uci.edu>...

You could also try the following two queries:

select right('create table ' + so1.name + '(' + '
', 255 * ( abs( sign(sc1.colid - 1) - 1 ) ) )+
sc1.name + ' ' +
st1.name + ' ' +
substring( '(' + rtrim( convert( char, sc1.length ) ) + ') ', 1,
patindex('%char', st1.name ) * 10 ) +
substring( '(' + rtrim( convert( char, sc1.prec ) ) + ', ' + rtrim(
convert( char, sc1.scale ) ) + ') ' , 1, patindex('numeric', st1.name ) *
10 ) +
substring( 'NOT NULL', ( convert( int, convert( bit,( sc1.status &
8 ) ) ) * 4 ) + 1, 8 * abs(convert(bit, (sc1.status & 0x80)) - 1 ) ) +
right('identity ', 9 * convert(bit, (sc1.status & 0x80)) ) +
right(',', 5 * ( convert(int,sc2.colid) - convert(int,sc1.colid) ) ) +
right(' )
' + 'go' + '
' + '
', 255 * abs( sign( ( convert(int,sc2.colid) - convert(int,sc1.colid) ) ) -
1 ) )
from sysobjects so1,
syscolumns sc1,
syscolumns sc2,
systypes st1
where so1.type = 'U'
and sc1.id = so1.id
and st1.usertype = sc1.usertype
and sc2.id = sc1.id
and sc2.colid = (select max(colid)
from syscolumns
where id = sc1.id)
order by so1.name, sc1.colid


select so1.name,
sc1.name + ' ' +
st1.name + ' ' +
substring( '(' + rtrim( convert( char, sc1.length ) ) + ') ', 1,
patindex('%char', st1.name ) * 10 ) +
substring( 'NOT NULL', ( convert( int, convert( bit,( sc1.status & 8
) ) ) * 4 ) + 1, 8 )
from sysobjects so1,
syscolumns sc1,
systypes st1
where so1.type = 'U'
and so1.id = sc1.id
and st1.usertype = sc1.usertype
order by so1.name, sc1.colid

Cheers
Phil

===============================================================
= Phil Allen Ph/Fax: +61 6 288 2417 =
= IT Consultant Mobile: +61 0412 040 520 =
= 53 Folingsby St Data: +61 6 287 3239 =
= Weston ACT 2611 EMail: ph...@ozemail.com.au =
= Australia =
===============================================================


J.Masino/T.Larson

unread,
Oct 3, 1996, 3:00:00 AM10/3/96
to

Bryon Lape (bl...@utk.edu) wrote:
: Is there a sybase command that I am not seeing or can I get sybase to
: just give me the structure of a table? That is, its name and the columns
: associated with it and their data types. Nothing else. And in a usable form
: as in one line of data per row, not 5 lines like what sp_columns spews out.

Check out the ISUG web site http://www.sybase/com/ISUG and follow the link
for "Code". There's a procedure called sp_describe that I think gives you
what you want.

Teresa Larson

____________________________________________________________________
/ Teresa A. Larson ISUG Electronic Media Chair /
/ Bell Atlantic Voice: (301) 282-0051 /
/ 13100 Columbia Pike, A-3-3 Fax: (301) 282-9416 /
/ Silver Spring, MD 20904 Teresa....@bell-atl.com /
/___________________________________________________________________/
#include <std_disclaimer>

Edward Barlow

unread,
Oct 7, 1996, 3:00:00 AM10/7/96
to

Try downloaded my extended stored procedure library. There are a
variety of "better" procs there that give you this type of info.

Cheers
Ed
http://www.tiac.net/users/sqltech


"Phil Allen" <ph...@ozemail.com.au> wrote:

>Chris Markle <cma...@abu.bio.uci.edu> wrote in article
><cmarkle....@abu.bio.uci.edu>...
>> bl...@utk.edu (Bryon Lape) writes:
>>

>> > Is there a sybase command that I am not seeing or can I get sybase to
>> >just give me the structure of a table? That is, its name and the
>columns
>> >associated with it and their data types. Nothing else. And in a usable
>form
>> >as in one line of data per row, not 5 lines like what sp_columns spews
>out.
>>

Tony Findlater

unread,
Oct 9, 1996, 3:00:00 AM10/9/96
to Bryon Lape

Try using sp_help <table name> .
0 new messages