Hi,

Is it possible to drop all the primary keys, Index and tables created by user. Please share me the SQL query to achieve this objective.

check syscat.tables, syscat.indexes and syscat.constraints, see the field "create user" then generate the sql

Hi,

I would Like to know if we can achieve this Via Batch process in mainframe to drop all the indexes, primary key and tables associated to a userid.

It is sufficient to just drop tables; all dependent indexes and constraints, including PKs, will be dropped automatically.

Thanks for the clarification

perumj1, since you are on the mainframe (z/os I assume), it also depends on how your system is set up.

If every user has a separate table space, you can drop the table space which will get rid of all dependent objects (tables, Indexes, etc.).

If every user has a separate database, you can drop the database which will get rid of all dependent objects (table spaces, tables, Indexes, etc.).

Note: One item that will not be dropped automatically is any Aliases. These have to be dropped separately. Additionally, any Plans or Packages will have to be freed to remove them from the system (they will only be invalidated if the dependent tables / indexes are dropped).

Wilson,

Yes I am from Z/os. Here My challenge is to just free up the tablespaces for One Database. Since No users were able to insert any tables any more and they were getting an error of Sqlcode = -497 error.