Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Fetching Sybase Constraints metadata using Select queries

6 posts in General Discussion Last posting was on 2009-11-05 16:28:09.0Z
Avadhoot Kulkarni Posted on 2009-11-05 12:42:16.0Z
Sender: 6a73.4af2c351.1804289383@sybase.com
From: Avadhoot Kulkarni
Newsgroups: sybase.public.ase.general
Subject: Fetching Sybase Constraints metadata using Select queries
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4af2c828.6b1d.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 5 Nov 2009 04:42:16 -0800
X-Trace: forums-1-dub 1257424936 10.22.241.41 (5 Nov 2009 04:42:16 -0800)
X-Original-Trace: 5 Nov 2009 04:42:16 -0800, 10.22.241.41
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28614
Article PK: 77853

Hi,

I know that sybase ASE exposes the metadata through system
procedures But for my need, I want to write plain sql select
queries to achieve this.
I am specifically stuck with queries which can list all
constraints and all constraint columns.
information required will be

CATALOG NAME
SCHEMA NAME
TABLE NAME
CONSTRAINT NAME
CONSTRAINT COLUMN NAME
CONSTRAINT TYPE
(Like 'Primary Key', 'Foreign Key', 'Check', 'Identity',
'Unique' etc.)

Has anyone done this before? Can anyone suggests me how can
I achieve this.

Thanks in anticipation


Carl Kayser Posted on 2009-11-05 13:31:58.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4af2c828.6b1d.1681692777@sybase.com>
Subject: Re: Fetching Sybase Constraints metadata using Select queries
Lines: 45
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3598
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af2d3ce$1@forums-1-dub>
Date: 5 Nov 2009 05:31:58 -0800
X-Trace: forums-1-dub 1257427918 10.22.241.152 (5 Nov 2009 05:31:58 -0800)
X-Original-Trace: 5 Nov 2009 05:31:58 -0800, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28615
Article PK: 77855


<Avadhoot Kulkarni> wrote in message
news:4af2c828.6b1d.1681692777@sybase.com...
> Hi,
>
> I know that sybase ASE exposes the metadata through system
> procedures But for my need, I want to write plain sql select
> queries to achieve this.
> I am specifically stuck with queries which can list all
> constraints and all constraint columns.
> information required will be
>
> CATALOG NAME
> SCHEMA NAME
> TABLE NAME
> CONSTRAINT NAME
> CONSTRAINT COLUMN NAME
> CONSTRAINT TYPE
> (Like 'Primary Key', 'Foreign Key', 'Check', 'Identity',
> 'Unique' etc.)
>
> Has anyone done this before? Can anyone suggests me how can
> I achieve this.
>
> Thanks in anticipation

(1) Since there is no CATALOG NAME or SCHEMA NAME in ASE ... well, what do
you mean? Do you want every constraint in every database on the ASE server?

(2) Regardless of (1) do you want singular column names (must switch away
from the upper case usage of the OP!) or will it contain all of the columns
of a PK, etc. In other words a single row with concatenated columns or
multiple rows? The former will be difficult to do with "plain old SQL" than
the latter.

(3) Since FKs can reference PKs in other databases your desired list seems
inadequate. Wouldn't you want to know something about the PK
database/table?

(4) PKs in proxy databases (DB links?) might present a problem.

(5) My first guess is that the "plain old SQL" will be a union based mostly
on each constraint type.


Avadhoot Kulkarni Posted on 2009-11-05 14:06:34.0Z
Sender: 6a73.4af2c351.1804289383@sybase.com
From: Avadhoot Kulkarni
Newsgroups: sybase.public.ase.general
Subject: Re: Fetching Sybase Constraints metadata using Select queries
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4af2dbea.6e27.1681692777@sybase.com>
References: <4af2d3ce$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 5 Nov 2009 06:06:34 -0800
X-Trace: forums-1-dub 1257429994 10.22.241.41 (5 Nov 2009 06:06:34 -0800)
X-Original-Trace: 5 Nov 2009 06:06:34 -0800, 10.22.241.41
Lines: 53
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28617
Article PK: 77857

Thanks Mark and Carl,

I have already seen the implementation of those SPs through
Sybase Central. They were helpful but use a lot of cursor
fetching and more temp table usage to the likes of 'plain
old SQL'


To Answer Carl's questions
(1) Since there is no CATALOG NAME or SCHEMA NAME in ASE
.. well, what do
you mean? Do you want every constraint in every database on
the ASE server?
=>i am fine with
catalog --> Db Name ==> db_name()
schema --> user name ==> user_name(userid)
so these columns are not challenge for me.

(2) Regardless of (1) do you want singular column names
(must switch away from the upper case usage of the OP!) or
will it contain all of the columns
of a PK, etc. In other words a single row with concatenated
columns or
multiple rows? The former will be difficult to do with
"plain old SQL" than the latter.
=>Unfortunately, I want multiple rows per constraint if that
involves multiple columns. But I guess I can manage that
with unions with one column per query.

(3) Since FKs can reference PKs in other databases your
desired list seems
inadequate. Wouldn't you want to know something about the
PK
database/table?
=> True, I should be looking for that too.

(4) PKs in proxy databases (DB links?) might present a
problem.
=> I am ready to ignore them for now.

(5) My first guess is that the "plain old SQL" will be a
union based mostly
on each constraint type.
=> on this suggestion particularly, what kind of system
table combination will give me these required details. I
tried syskeys, sysindexes, sysconstraints, sysobjects,
syscolumns sysreferences for my use. but not knowing where
to look exactly and having so much to look at kind of
overwhelms me.


"Mark A. Parsons" <iron_horse Posted on 2009-11-05 14:22:51.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Fetching Sybase Constraints metadata using Select queries
References: <4af2d3ce$1@forums-1-dub> <4af2dbea.6e27.1681692777@sybase.com>
In-Reply-To: <4af2dbea.6e27.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091104-0, 11/04/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af2dfbb@forums-1-dub>
Date: 5 Nov 2009 06:22:51 -0800
X-Trace: forums-1-dub 1257430971 10.22.241.152 (5 Nov 2009 06:22:51 -0800)
X-Original-Trace: 5 Nov 2009 06:22:51 -0800, vip152.sybase.com
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28618
Article PK: 77858


Avadhoot Kulkarni wrote:
> I have already seen the implementation of those SPs through
> Sybase Central. They were helpful but use a lot of cursor
> fetching and more temp table usage to the likes of 'plain
> old SQL'

... snip ...

> (5) My first guess is that the "plain old SQL" will be a
> union based mostly
> on each constraint type.
> => on this suggestion particularly, what kind of system
> table combination will give me these required details. I
> tried syskeys, sysindexes, sysconstraints, sysobjects,
> syscolumns sysreferences for my use. but not knowing where
> to look exactly and having so much to look at kind of
> overwhelms me.

*where* to find the desired constraint info can be determined from the source code of sp_helpconstraint.

I'd suggest you start with one constraint type, make sure you have a couple instances in your database, then experiment
with a *copy* of sp_helpconstraint to see if you can zero in on the pieces of the SQL which address the desired
constraint type.

----------------

Other options you have for pulling the constraint info:

1 - parse the output from sp_helpconstraint

2 - if running ASE 12.5+, use the ddlgen program to reverse engineer the constraints; you may need to further parse this
output depending on what exactly you're looking to accomplish with the constraint info

3 - do a web search for SQL/scripts (eg, www.edbarlow.com) which have already been written to reverse engineer
constraint details; again, you may need to further parse the output; obviously (?) if you find some SQL to reverse
engineer the contraints you may be able to look at said SQL for tips on how to write your own code


"Mark A. Parsons" <iron_horse Posted on 2009-11-05 13:47:35.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Fetching Sybase Constraints metadata using Select queries
References: <4af2c828.6b1d.1681692777@sybase.com>
In-Reply-To: <4af2c828.6b1d.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091104-0, 11/04/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af2d777$1@forums-1-dub>
Date: 5 Nov 2009 05:47:35 -0800
X-Trace: forums-1-dub 1257428855 10.22.241.152 (5 Nov 2009 05:47:35 -0800)
X-Original-Trace: 5 Nov 2009 05:47:35 -0800, vip152.sybase.com
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28616
Article PK: 77856

In addition to Carl's comments ...

Since you know the ASE system procedures provide the info you're looking for, take a look at the source code for said
procedures to see where/how they are obtaining their information. From this you should be able to design your own query(s).

The source code for system stored procs can be found in the sybsystemprocs database, or in the installmaster script
(your DBA should know where this script is located).

Avadhoot Kulkarni wrote:
> Hi,
>
> I know that sybase ASE exposes the metadata through system
> procedures But for my need, I want to write plain sql select
> queries to achieve this.
> I am specifically stuck with queries which can list all
> constraints and all constraint columns.
> information required will be
>
> CATALOG NAME
> SCHEMA NAME
> TABLE NAME
> CONSTRAINT NAME
> CONSTRAINT COLUMN NAME
> CONSTRAINT TYPE
> (Like 'Primary Key', 'Foreign Key', 'Check', 'Identity',
> 'Unique' etc.)
>
> Has anyone done this before? Can anyone suggests me how can
> I achieve this.
>
> Thanks in anticipation


J Posted on 2009-11-05 16:28:09.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Fetching Sybase Constraints metadata using Select queries
Reply-To: J@bogusemailAddress.com
Message-ID: <4af2fcd7.2699221@forums.sybase.com>
References: <4af2c828.6b1d.1681692777@sybase.com> <4af2d777$1@forums-1-dub>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 5 Nov 2009 08:28:09 -0800
X-Trace: forums-1-dub 1257438489 10.22.241.152 (5 Nov 2009 08:28:09 -0800)
X-Original-Trace: 5 Nov 2009 08:28:09 -0800, vip152.sybase.com
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28619
Article PK: 77859

On 5 Nov 2009 05:47:35 -0800, "Mark A. Parsons"
<iron_horse@no_spamola.compuserve.com> wrote:

Additionally, the ODBC and JDBC (jConnect) stored procedures access
ASE schema. If you can find a procedure there you can also see its
source code or use it.

Jay

>In addition to Carl's comments ...
>
>Since you know the ASE system procedures provide the info you're looking for, take a look at the source code for said
>procedures to see where/how they are obtaining their information. From this you should be able to design your own query(s).
>
>The source code for system stored procs can be found in the sybsystemprocs database, or in the installmaster script
>(your DBA should know where this script is located).
>
>Avadhoot Kulkarni wrote:
>> Hi,
>>
>> I know that sybase ASE exposes the metadata through system
>> procedures But for my need, I want to write plain sql select
>> queries to achieve this.
>> I am specifically stuck with queries which can list all
>> constraints and all constraint columns.
>> information required will be
>>
>> CATALOG NAME
>> SCHEMA NAME
>> TABLE NAME
>> CONSTRAINT NAME
>> CONSTRAINT COLUMN NAME
>> CONSTRAINT TYPE
>> (Like 'Primary Key', 'Foreign Key', 'Check', 'Identity',
>> 'Unique' etc.)
>>
>> Has anyone done this before? Can anyone suggests me how can
>> I achieve this.
>>
>> Thanks in anticipation