Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query in ISQL to export schema

Posted on 1999-01-13
4
Medium Priority
?
1,453 Views
Last Modified: 2013-11-19
Looking for the equivalent sybase select statement for the oracle statement below: This statement uses metadata to export the Table name, column name, data type & length, description/definition and all other attributes

SELECT OWNER,TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID
FROM ALL_TAB_COLUMNS
where OWNER = 'ME'
0
Comment
Question by:rickwinterkorn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 

Expert Comment

by:tgensler
ID: 1098546
This code was taken from simon walkers SQL workshop site on the internet.

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

 sp__cols

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

 Copyright 1992-1995 The SQL Workshop Ltd.      siwalker@cix.compulink.co.uk

 Who      When      Why
 ---------------------------------------------------------------------------
 npike      ???      created
 simon      5jul92      tidied up, add error handling
 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

/*
** This procedure copyright 1995 The SQL Workshop Ltd.
** The code is freely distributable, but please leave this comment intact.
** simon walker
** siwalker@cix.compulink.co.uk
*/

declare      @objid      int

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

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


0
 

Author Comment

by:rickwinterkorn
ID: 1098547
Thanks for the effort tgensler, however it didn't work.

0
 

Author Comment

by:rickwinterkorn
ID: 1098548
Answer - I wrote the following code and it works very well,
thought I'd share it.

select T.creator, C.tname, C.colno, C.cname, C.coltype,C.length,C.in_primary_key, C.nulls, C.remarks
from SYSCOLUMNS C, SYSTABLE T
where Trim(C.creator) = 'DBA'
and Trim(C.tname) = Trim(T.table_name);

output to c:\DirName\FileName.txt
format ascii;
0
 
LVL 2

Accepted Solution

by:
ajith_29 earned 100 total points
ID: 1098549
Hi rick,
This script will do the work for you

select Table_name = o.name ,
       Column_name= c.name,
          Data_type   = t.name,
             Length = c.length,
             Prec = c.prec,
             Scale = c.scale,
             Nulls = convert(bit, (c.status & 8))
            from syscolumns c, systypes t,sysobjects o
            where c.id <>0 and c.id = o.id and o.type = 'U'
                  and c.usertype *= t.usertype



Regards
Ajith
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question