Q: easy way to list columns on a table in ISQL?

2256 views
Skip to first unread message

Robert M. Zegarac

unread,
Jan 12, 1996, 3:00:00 AM1/12/96
to
Is there an easy was in ISQL to list the columns contained in a table?
I find myself in ISQL and don't remember col names I want to query on.
Then I have to dig out the doco. I vaguely remember a command (I think
it was DRAW) that was used in QMF (DB/2) to create a query that listed
every column. Is there something similar? TIA.

--

--
Robert M. Zegarac
lnusspo...@eds.com

Naresh K Bhatia

unread,
Jan 12, 1996, 3:00:00 AM1/12/96
to

In a previous article, lnusspo...@eds.com (Robert M. Zegarac) says:

>Is there an easy was in ISQL to list the columns contained in a table?
>I find myself in ISQL and don't remember col names I want to query on.
>Then I have to dig out the doco. I vaguely remember a command (I think
>it was DRAW) that was used in QMF (DB/2) to create a query that listed
>every column. Is there something similar? TIA.
>
>--
>

You can use

1> sp_help table_name
2> go


or


1> select * from table_name where 2 = 1
2> go

This will give you all the column name in output.
--
Naresh Bhatia

Terry Miesse

unread,
Jan 12, 1996, 3:00:00 AM1/12/96
to
Check out sp_help <tablename>

In article <4d5nol$g...@maverick.tad.eds.com>, "Robert M. Zegarac" <lnusspo...@eds.com> writes:
|> Is there an easy was in ISQL to list the columns contained in a table?
|> I find myself in ISQL and don't remember col names I want to query on.
|> Then I have to dig out the doco. I vaguely remember a command (I think
|> it was DRAW) that was used in QMF (DB/2) to create a query that listed
|> every column. Is there something similar? TIA.
|>
|> --
|>

|> --
|> Robert M. Zegarac
|> lnusspo...@eds.com
|>
|>

--

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Terry Miesse | All opinions are mine.
LEXIS/NEXIS | All quotes are somebody else's.
I/S Production Services |
(513) 865-6800 x6711 | Do what you always did
terry....@lexis-nexis.com Get what you always got
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Matthew D. Healy

unread,
Jan 13, 1996, 3:00:00 AM1/13/96
to
In article <4d5r7b$f...@meaddata.lexis-nexis.com>, ter...@lexis-nexis.com
(Terry Miesse) wrote:

: Check out sp_help <tablename>


:
: In article <4d5nol$g...@maverick.tad.eds.com>, "Robert M. Zegarac"
<lnusspo...@eds.com> writes:
: |> Is there an easy was in ISQL to list the columns contained in a table?
: |> I find myself in ISQL and don't remember col names I want to query on.

I generally do the following:

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
go


Save this as a text file on whatever machine you use for
telnetting to isql, and then you can just cut-and-paste.
Or you could make a little stored procedure out of it.

It does look nicer with your terminal in 132-col
mode, and the -w132 flag on the isql commandline -- the
output is too wide for 80 columns, so you get ugly
wrapping!

I am aware that you can get this info by first typing sp_help
for a list of tables, then typing sp_help tablename for the
fields, etc. However, I prefer this for several reasons:

sp_help gives only one table at a time; mine gives the
whole database at once

sp_help gives lots of details that I don't often need; mine
gives the things I most often need

I think my format is cleaner :-)

My script gives fully-qualified fieldnames, ready to cut
and paste into SQL commands.

I have used the above sql with the following versions of
Sybase (as given by typing "print @@version" into isql):

SQL Server/10.0.1/
P2/Sun4/OS 4.1.x/1/OPT/
Tue Apr 5 03:38:51 PDT 1994

SQL Server/4.9.1/EBF 1440 Rollup/
Sun4/OS 4.1.2/1/OPT/
Wed Mar 17 20:25:05 PST

--------
Matthe...@yale.edu Postdoc (& now, WebMaster)
Center for Medical Informatics, Yale School of Medicine
He was covered with clanking chains of code. "I am the
Ghost of Hacks Past," he said to Scrooge.
http://paella.med.yale.edu/~healy/matt_healy.html

fo...@midwest.net

unread,
Jan 19, 1996, 3:00:00 AM1/19/96
to
I saw a very cute very little trick in LA last year.
try it...
select * from <tablename> where 1 = 2

Of course 1 almost never equals 2, therefore ISQL prints out the column
names only.

>In article <4d5nol$g...@maverick.tad.eds.com>, "Robert M. Zegarac" <lnusspo...@eds.com> writes:
>|> Is there an easy was in ISQL to list the columns contained in a table?
>|> I find myself in ISQL and don't remember col names I want to query on.

Rusty Alderson

unread,
Jan 30, 1996, 3:00:00 AM1/30/96
to

In Article<4dn9u2$n...@sparky.midwest.net>, <fo...@midwest.net> write:
> Path: gryphon.phoenix.net!news.sprintlink.net!sparky.midwest.net!usenet
> From: fo...@midwest.net
> Newsgroups: comp.databases.sybase
> Subject: Re: Q: easy way to list columns on a table in ISQL?
> Date: 19 Jan 1996 05:24:18 GMT
> Organization: Midwest Internet
> Lines: 32
> Message-ID: <4dn9u2$n...@sparky.midwest.net>
> References: <4d5nol$g...@maverick.tad.eds.com> <4d5r7b$f...@meaddata.lexis-nexis.com>
> Reply-To: fo...@midwest.net
> NNTP-Posting-Host: cdale27.midwest.net
> X-Newsreader: IBM NewsReader/2 v1.2

I'm not sure if the context of this solution will work
in the context of the question, but how about:

select name
from syscolumns
where id=object_id(<tablename>)

Matthew D. Healy

unread,
Jan 31, 1996, 3:00:00 AM1/31/96
to
[previous discussion deleted]

I generally use the following SQL code:

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


It gives fully-qualified column names, ready for cutting
and pasting into a program.

NOTE: For a clean output with ISQL, before launching ISQL,
set your terminal window in 132 column mode, and use
the -w132 flag on the ISQL commandline. Otherwise, the output
will give ugly wrapping.

I regularly use this with both Sybase 4.x and Sybase 10.x servers.

Simon Walker

unread,
Jan 31, 1996, 3:00:00 AM1/31/96
to
Robert M. Zegarac wrote:
> Is there an easy was in ISQL to list the columns contained in a table?
[snip]

Some others replied:

> [various long-winded manual SQL queries]

Hmmm. Obviously all the people who responded to this message really
enjoy typing...
Stored procedures have been known to make life easier in this department.
What you do is extract the text of sp_help, and ruthlessly plagiarise the
useful bit to create your own little column info procedure.

Here's one I prepared earlier:

Cheers,

simon walker
The SQL Workshop
Milton Keynes, UK
siwa...@cix.compulink.co.uk

--- snip here ---

/*************************************************************************
**

sp__cols

list columns in table without having to wade through sp_help's excess
info

Handcrafted by the SQL Gnomes of
The SQL Workshop, 31 Newport Road, Woolstone, Milton Keynes, MK15 0AD
siwa...@cix.compulink.co.uk

Who When Why
-------------------------------------------------------------------------
--
simon longago created
simon 5jul92 tidied up, add error handling, inc. 10 changes
simon 31jan96 update for release

**************************************************************************
**/

if exists(select * from master..sysdatabases where name =
"sybsystemprocs")
use sybsystemprocs
else
use master
go

if exists(select * from sysobjects where name = "sp__cols" and type = "P")
drop procedure sp__cols
go

create procedure sp__cols

@objname char(30)
as

declare @objid int

select @objid = id
from sysobjects
where name = @objname
and type in ("U", "V")

if @objid is NULL
begin
print "Object is not a table or view in the current database."
return (1)
end

select 'column name' = c.name,
type = t.name,
length = c.length,
prec = c.prec,
scale = c.scale,
nulls = convert(bit, (c.status & 8)),
'default name' = object_name(c.cdefault),
'rule name' = object_name(c.domain),
ident = convert(bit, (c.status & 0x80))
from syscolumns c,
systypes t
where c.id = @objid
and c.usertype *= t.usertype

return (0)

go

grant execute on sp__cols to public
go


simon walker
The SQL Workshop
Milton Keynes, UK
siwa...@cix.compulink.co.uk

Francois de Kock

unread,
Jan 31, 1996, 3:00:00 AM1/31/96
to Rusty Alderson
Rusty Alderson wrote:
>
> In Article<4dn9u2$n...@sparky.midwest.net>, <fo...@midwest.net> write:
> > Path: gryphon.phoenix.net!news.sprintlink.net!sparky.midwest.net!usenet
> > From: fo...@midwest.net
> > Newsgroups: comp.databases.sybase
> > Subject: Re: Q: easy way to list columns on a table in ISQL?
> > Date: 19 Jan 1996 05:24:18 GMT
> > Organization: Midwest Internet
> > Lines: 32
> > Message-ID: <4dn9u2$n...@sparky.midwest.net>
> > References: <4d5nol$g...@maverick.tad.eds.com> <4d5r7b$f...@meaddata.lexis-nexis.com>
> > Reply-To: fo...@midwest.net
> > NNTP-Posting-Host: cdale27.midwest.net
> > X-Newsreader: IBM NewsReader/2 v1.2
> >
> > I saw a very cute very little trick in LA last year.
> > try it...
> > select * from <tablename> where 1 = 2
> >
> > Of course 1 almost never equals 2, therefore ISQL prints out the column
> > names only.
> >
> > >In article <4d5nol$g...@maverick.tad.eds.com>, "Robert M. Zegarac" <lnusspo...@eds.com> writes:
> > >|> Is there an easy was in ISQL to list the columns contained in a table?
> > >|> I find myself in ISQL and don't remember col names I want to query on.
> > >|> Then I have to dig out the doco. I vaguely remember a command (I think
> > >|> it was DRAW) that was used in QMF (DB/2) to create a query that listed
> > >|> every column. Is there something similar? TIA.
> > >|>
> > >|> --
> > >|>
> > >|> --
> > >|> Robert M. Zegarac
> > >|> lnusspo...@eds.com
> > >|>
> > >|>
> > >
> > >--
> > >
> > >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > >Terry Miesse | All opinions are mine.
> > >LEXIS/NEXIS | All quotes are somebody else's.
> > >I/S Production Services |
> > >(513) 865-6800 x6711 | Do what you always did
> > >terry....@lexis-nexis.com Get what you always got
> > >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >
>
> I'm not sure if the context of this solution will work
> in the context of the question, but how about:
>
> select name
> from syscolumns
> where id=object_id(<tablename>)

Use the system stored procedure "sp_help <table_name> to get a list of
all columns in the table as well as collumn data types.

Stefan Mahs

kevin.n...@gmail.com

unread,
Apr 5, 2016, 10:45:46 AM4/5/16
to
On Friday, January 12, 1996 at 3:00:00 AM UTC-5, Terry Miesse wrote:
> Check out sp_help <tablename>
>
> In article <4d5nol$g...@maverick.tad.eds.com>, "Robert M. Zegarac" <lnusspo...@eds.com> writes:
> |> Is there an easy was in ISQL to list the columns contained in a table?
> |> I find myself in ISQL and don't remember col names I want to query on.
> |> Then I have to dig out the doco. I vaguely remember a command (I think
> |> it was DRAW) that was used in QMF (DB/2) to create a query that listed
> |> every column. Is there something similar? TIA.
> |>
> |> --
> |>
> |> --
> |> Robert M. Zegarac
> |> lnusspo...@eds.com
> |>
> |>
>
> --
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Terry Miesse | All opinions are mine.
> LEXIS/NEXIS | All quotes are somebody else's.
> I/S Production Services |
> (513) 865-6800 x6711 | Do what you always did
> terry....@lexis-nexis.com Get what you always got
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

thank you very much. that was a great help
Reply all
Reply to author
Forward
0 new messages