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.

Owner Identification

3 posts in Product Futures Discussion Last posting was on 2003-03-03 03:11:24.0Z
Carl Kayser Posted on 2003-02-28 18:08:34.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Subject: Owner Identification
Date: Fri, 28 Feb 2003 13:08:34 -0500
Lines: 18
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <ID8q5c13CHA.181@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 146.142.35.25
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:976
Article PK: 95216

This is a little bit rough, but...

(1) Unless there is something new one cannot determine the owner of a FK/PK
table in a different database via sysreferences.

(2) The monitoring tables generally don't have a UID column. (Possible
exceptions: OwnerUID on MonCachedProcedures and monProcessProcedures;
OwnerUserID on monCachedObject and monProcessObject.) So if I want to see
which indexes are not used by querying monOpenObjectActivity and there are
same-named tables in a database ... am I stuck with determining the owner?
(Other than the hard way: get the objectID, explicitly do "use daabase",
etc. In other words, there is no simple or elegant way to do this within
"straight SQL" or within a stored procedure.)

So, perhaps there is a need for, say, user_name (user_id [,db_id])? Or,
better yet, owner_name (object_id, db_id)!


Carl Kayser Posted on 2003-03-02 10:49:56.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
References: <ID8q5c13CHA.181@forums.sybase.com>
Subject: Re: Owner Identification
Date: Sun, 2 Mar 2003 05:49:56 -0500
Lines: 34
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <bASPDxK4CHA.245@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 146.142.35.25
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:979
Article PK: 95217

Since one may sometines want login names and sometimes user names I would
want

owner_name (object_id [, db_id])
owner_id (object_id [,db_id])
sowner_name (object_id [, db_id])
sowner_id (object_id [,db_id])

The above names would be consistent with user_name (), suser_name (), etc.

"Carl Kayser" <kayser_c@bls.gov> wrote in message
news:ID8q5c13CHA.181@forums.sybase.com...
> This is a little bit rough, but...
>
> (1) Unless there is something new one cannot determine the owner of a
FK/PK
> table in a different database via sysreferences.
>
> (2) The monitoring tables generally don't have a UID column. (Possible
> exceptions: OwnerUID on MonCachedProcedures and monProcessProcedures;
> OwnerUserID on monCachedObject and monProcessObject.) So if I want to see
> which indexes are not used by querying monOpenObjectActivity and there are
> same-named tables in a database ... am I stuck with determining the owner?
> (Other than the hard way: get the objectID, explicitly do "use daabase",
> etc. In other words, there is no simple or elegant way to do this within
> "straight SQL" or within a stored procedure.)
>
> So, perhaps there is a need for, say, user_name (user_id [,db_id])? Or,
> better yet, owner_name (object_id, db_id)!
>
>


putnamr Posted on 2003-03-03 03:11:24.0Z
From: putnamr@river.it.gvsu.edu
Date: Sun, 2 Mar 2003 22:11:24 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Owner Identification
Message-ID: <580555264EA4F3CC0011863485256CDE.0040CF7585256CDD@webforums>
References: <ID8q5c13CHA.181@forums.sybase.com> <bASPDxK4CHA.245@forums.sybase.com>
Lines: 62
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:981
Article PK: 95220

Hello,

Good feature request. I have requested this about 5 times through ISUG
over the past two years. No success and no response. I have not had any
luck with using ISUG. Maybe you will get a better response through this
process.

Here is a view that gives fk constraint info in a very user friendly
fashion.

CREATE VIEW SINF_FKCONSTRAINTS
AS
-- Author: Ryan Putnam
-- Intent: Catalog View

SELECT ISNULL(USER_NAME(so.uid), 'dbo') AS 'OWNER'
,OBJECT_NAME(sr.tableid, sr.frgndbid) AS 'OBJNAME'
,OBJECT_NAME(sr.constrid, sr.frgndbid) AS 'CONSTRNAME'
,sr.constrid AS 'CONSTRID'
,sr.tableid AS 'OBJID'
,sr.keycnt AS 'NUMKEYS'
,ISNULL(sr.frgndbname,DB_NAME()) AS 'FK_DB'
,OBJECT_NAME(sr.tableid, sr.frgndbid) AS 'FK_TB'
,ISNULL(sr.pmrydbname,DB_NAME()) AS 'PK_DB'
,OBJECT_NAME(sr.reftabid, DB_ID(sr.pmrydbname) ) AS 'PK_TB'
,COL_NAME(sr.tableid,sr.fokey1,DB_ID(sr.frgndbname)) AS 'FKEY_1'
,COL_NAME(sr.tableid,sr.fokey2,DB_ID(sr.frgndbname)) AS 'FKEY_2'
,COL_NAME(sr.tableid,sr.fokey3,DB_ID(sr.frgndbname)) AS 'FKEY_3'
,COL_NAME(sr.tableid,sr.fokey4,DB_ID(sr.frgndbname)) AS 'FKEY_4'
,COL_NAME(sr.tableid,sr.fokey5,DB_ID(sr.frgndbname)) AS 'FKEY_5'
,COL_NAME(sr.tableid,sr.fokey6,DB_ID(sr.frgndbname)) AS 'FKEY_6'
,COL_NAME(sr.tableid,sr.fokey7,DB_ID(sr.frgndbname)) AS 'FKEY_7'
,COL_NAME(sr.tableid,sr.fokey8,DB_ID(sr.frgndbname)) AS 'FKEY_8'
,COL_NAME(sr.tableid,sr.fokey9,DB_ID(sr.frgndbname)) AS 'FKEY_9'
,COL_NAME(sr.tableid,sr.fokey10,DB_ID(sr.frgndbname)) AS 'FKEY_10'
,COL_NAME(sr.tableid,sr.fokey11,DB_ID(sr.frgndbname)) AS 'FKEY_11'
,COL_NAME(sr.tableid,sr.fokey12,DB_ID(sr.frgndbname)) AS 'FKEY_12'
,COL_NAME(sr.tableid,sr.fokey13,DB_ID(sr.frgndbname)) AS 'FKEY_13'
,COL_NAME(sr.tableid,sr.fokey14,DB_ID(sr.frgndbname)) AS 'FKEY_14'
,COL_NAME(sr.tableid,sr.fokey15,DB_ID(sr.frgndbname)) AS 'FKEY_15'
,COL_NAME(sr.tableid,sr.fokey16,DB_ID(sr.frgndbname)) AS 'FKEY_16'
,COL_NAME(sr.reftabid,sr.refkey1,DB_ID(sr.pmrydbname)) AS 'PKEY_1'
,COL_NAME(sr.reftabid,sr.refkey2,DB_ID(sr.pmrydbname)) AS 'PKEY_2'
,COL_NAME(sr.reftabid,sr.refkey3,DB_ID(sr.pmrydbname)) AS 'PKEY_3'
,COL_NAME(sr.reftabid,sr.refkey4,DB_ID(sr.pmrydbname)) AS 'PKEY_4'
,COL_NAME(sr.reftabid,sr.refkey5,DB_ID(sr.pmrydbname)) AS 'PKEY_5'
,COL_NAME(sr.reftabid,sr.refkey6,DB_ID(sr.pmrydbname)) AS 'PKEY_6'
,COL_NAME(sr.reftabid,sr.refkey7,DB_ID(sr.pmrydbname)) AS 'PKEY_7'
,COL_NAME(sr.reftabid,sr.refkey8,DB_ID(sr.pmrydbname)) AS 'PKEY_8'
,COL_NAME(sr.reftabid,sr.refkey9,DB_ID(sr.pmrydbname)) AS 'PKEY_9'
,COL_NAME(sr.reftabid,sr.refkey10,DB_ID(sr.pmrydbname)) AS 'PKEY_10'
,COL_NAME(sr.reftabid,sr.refkey11,DB_ID(sr.pmrydbname)) AS 'PKEY_11'
,COL_NAME(sr.reftabid,sr.refkey12,DB_ID(sr.pmrydbname)) AS 'PKEY_12'
,COL_NAME(sr.reftabid,sr.refkey13,DB_ID(sr.pmrydbname)) AS 'PKEY_13'
,COL_NAME(sr.reftabid,sr.refkey14,DB_ID(sr.pmrydbname)) AS 'PKEY_14'
,COL_NAME(sr.reftabid,sr.refkey15,DB_ID(sr.pmrydbname)) AS 'PKEY_15'
,COL_NAME(sr.reftabid,sr.refkey16,DB_ID(sr.pmrydbname)) AS 'PKEY_16'
FROM sysreferences sr
, sysobjects so
WHERE OBJECT_NAME(sr.tableid, sr.frgndbid) IS NOT NULL
AND OBJECT_NAME(sr.constrid, sr.frgndbid) IS NOT NULL
AND sr.constrid *= so.id
AND so.type = "U"