Results 1 to 8 of 8

Thread: User Tables

  1. #1
    Join Date
    Nov 2005
    Posts
    7

    Question Unanswered: User Tables

    Hi guys
    How can I retrieve the list of user-defined tables?
    The tables have been created by user-defined users and groups? not system tables?
    (select * from systable)

  2. #2
    Join Date
    Nov 2005
    Posts
    7
    Quote Originally Posted by gheiby
    Hi guys
    How can I retrieve the list of user-defined tables?
    The tables have been created by user-defined users and groups? not system tables?
    (select * from systable)
    I mean like the sybase central tool is doing? just show me the list of tables witch have been created by the users not system users and groups.

  3. #3
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    In ASE you can get a list of all usertables with select name from sysobjects where type like "U%"
    I'm not crazy, I'm an aeroplane!

  4. #4
    Join Date
    Nov 2005
    Posts
    7

    Unhappy no exactly

    thanks for the reply
    but that return every user-table.
    Actually i'm trying to get the list of tables have been created only by the users that have been created by users or groups not database.

    for example, that query return a table named RowGenerator witch has been created by dbo when database created, not me.
    I fyou know what do I mean?

    so I changed the query to the bellow one:

    SELECT *
    FROM SYSOBJECTS O LEFT OUTER JOIN SYSUSERPERMS U
    ON O.UID = U.USER_ID
    where type like 'U%' AND USER_NAME != 'dbo'

    but still it's not enoygh.
    for example I can see rs_threads witch has been created by rs_systabgroup.

    I need to get the list of tables like what Sybase Central Tool shows.
    List of tables have been created by users or groups witch those users or groups have not been created by Database.

  5. #5
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Run the request in Sybase Central, and then use the "show sql" option to see what the query looks like.

    Michael

  6. #6
    Join Date
    Nov 2005
    Posts
    7

    Unhappy

    My friend,
    I could not find Show SQL feature in Sybace Central.
    I'm using ASA 9.

  7. #7
    Join Date
    Nov 2005
    Posts
    7
    but when I was tracing the executed SQLs, I found the script below. But there is a table in select list named dbx_filter_list witch I think it's a temprary table. Because when I ron this query again, script could not find that table.


    SELECT T.table_id, T.table_name, U.user_name, T.table_type, F.dbspace_name, T.last_page, T.existing_obj, T.remote_location, T."replicate", T.view_def, P.constraint_name AS pkey_name, LIST( C.column_name, ', ' ORDER BY C.column_id ) AS pkey_column_list, IFNULL( AC.attribute_value, 'N', 'Y' ) AS pkey_clustered, T.primary_hash_limit, AP.attribute_value AS "pctfree", T.count, T.remarks

    FROM SYS.SYSTABLE T JOIN SYS.SYSUSERPERMS U ON U.user_id = T.creator JOIN SYS.SYSFILE F ON F.file_id = T.file_id LEFT OUTER JOIN SYS.SYSCONSTRAINT P ON P.table_id = T.table_id AND P.constraint_type = 'P' LEFT OUTER JOIN SYS.SYSCOLUMN C ON C.table_id = T.table_id AND C.pkey = 'Y' LEFT OUTER JOIN SYS.SYSATTRIBUTE AC ON AC.object_type = 'T' AND AC.object_id = T.table_id AND AC.attribute_id = 2 AND AC.attribute_value = 0 LEFT OUTER JOIN SYS.SYSATTRIBUTE AP ON AP.object_type = 'T' AND AP.object_id = T.table_id AND AP.attribute_id = 1

    WHERE T.table_type <> 'VIEW' AND U.user_id NOT IN (SELECT creator FROM dbx_filter_list)

    GROUP BY T.table_id, T.table_name, U.user_name, T.table_type, F.dbspace_name, T.last_page, T.existing_obj, T.remote_location, T."replicate", T.view_def, pkey_name, pkey_clustered, T.primary_hash_limit, "pctfree", T.count, T.remarks

    ORDER BY T.table_name, U.user_name

  8. #8
    Join Date
    Mar 2006
    Posts
    25

    Unhappy

    u use
    select name from sysobjects where type="U" and name not like "sys%"

    Quote Originally Posted by gheiby
    Hi guys
    How can I retrieve the list of user-defined tables?
    The tables have been created by user-defined users and groups? not system tables?
    (select * from systable)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •