List all tables for a particular object owner

[Former Member]

HI All,

I am trying to list all the objects for a particular owner using the below query.

select su.name + '.' + so.name from   sysobjects so,sysusers   su where  so.type = 'U' and so.uid  = su.uid order  by su.name,so.name

Isnt there a simple query using sp_iqhelp

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1540/doc/html/san12...

I just want to list for a particular owner.

[Former Member]

sp_iqtable is a good one too. But i wanted to get stuff from sp_iqhelp and sysobjects.

[jong-kil_park]

Hi,

If you want to just list up the tables which belong to a specific owner, please consider using this.

select 'DBA.'||table_name from systab where suser_name(creator) = 'DBA' order by 1 ;

You can substitute the DBA with the one you want.

Best regards,

Jerry

[markmumy]

My preference for this is just one slight variation:

     select suser_name(creator)||'.'||table_name

     from systable

     where UPPER( suser_name(creator) ) = 'DBA'  -- change this to alter object owner

     order by 1 ;

If you want all objects, then this will work using sp_iqhelp:

     select * from sp_iqhelp() where f2 = 'DBA'

Mark

[Former Member]

Mark,

The second query is interesting, querying using a store procedure i am seeing for the first time.

[markmumy]

Cool feature, yes?  It's been in IQ for a long, long time and I use it as much as possible.  Keeps me from having to reverse engineer code to do what I want.

Mark