Q: easy way to list columns on a table in ISQL?
Robert M. Zegarac
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
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
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
(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
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
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
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
> Is there an easy was in ISQL to list the columns contained in a table?
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
>
> 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:
> > >|> 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
> Check out sp_help <tablename>
>
> |> 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
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~