How to get DDL for database objects shared to othe

问题:

We are working on extracting database DDL of Sybase ASE 15.5 version. We have one sample created in java that execute ddl commands. We got ddl using ddlgen utility provided by Sybase with commands :

java -cp "mypath/lib/jconn4.jar;mypath/lib/dsparser.jar;mypath/lib/DDLGen.jar" com.sybase.ddlgen.DDLGenerator -Usa -Pmypassword -S192.123.13.111:5000 -Dmaster

Above command generate DDL for all database objects exist in user sa, also we get list of objects shared to other user by user sa as :

Grant Select on dbo.mytable1 to anotheruserthansa
go
Grant Select on dbo.mytable2 to anotheruserthansa
go 

Now we want DDL of shared objects by username, like in my case user is anotheruserthansa

We need some command that can generate DDL for user, we might not have its password. What we need to get DDL using my user sa, its password and with schema name : anotheruserthansa we need to generate ddl for all the database object those are shared by user sa to anotheruserthansa.

Like we do in Oracle with query :

select  object_type from dba_objects where owner = ''anotheruserthansa' and object_name = 'anotheruserthansa'

How can we get DDL for shared object using its schema?

回答1:

This would likely be done with a combination of a SQL Query, with parameters then passed to ddlgen

SELECT db_name()
     , su.name
     , so.type
     , so.name
 FROM sysusers su, sysobjects so
WHERE su.uid = so.uid
 AND su.name = "[name of user you are looking for]"

You can then pass the dbname, object type, and object name into ddlgen to generate the DDL.

ddlgen -Usa -Sservername -Tobject_type -Nobject_name -Ddbname ...etc

FYI - Sybase/SAP best practices recommends against allowing users to own objects within the database. It's recommended that all database objects be owned by dbo This is not to be confused with the concept of schemas used by Oracle and SQL Server. ASE does not use those kinds of logical divisions.