Sql-server – way to specify which schema the database task “Export Data-tier Application” exports for the other entities besides the tables

You need to look into using certificates to sign the stored procedures. This will allow users to access anothers tables.

I see two solutions. I did not test them but I hope they are userful.

In contrast to table mode export both user mode and tablespace mode export take care of the dependencies between tables so that the tables are imported in the right order. Both methods import the production table stuctures to dev. Tablespace mode export/import in ORacle 8i or 9i can be done only beteen database systems on the same operating system. On can handle this restriction by importing the data using user mode import into a intermediate schema or database on the same operating system as the target sytem.

Method 1: Using TablespaceMode export/import:
1) Put the tablespace(s) of prod in read only mode
2) TablespaceMode-Export the tablespace(s) and datafile copy from prod
3) Put the tablespaces of prod in read/write mode again
4) Drop the Tablespaces in dev (leaves procedures, views from dev)
5) TablespaceMode-Import the tablespaces in Target (includes tables, triggers , indexes, constraints of prod)
6) Put the tablespace(s) of dev in read/write mode

Maybe there are some problems you have to copy with changes in table structure, missing grants or that sequences have to be adjusted

Method 2: replace procedures by an export
1) UserMod-Export of the prod schema
2) UserMod-Export of the dev schema
CONSTRAINTS=N
GRANTS=N
STATITICS=N
TRIGGERS=N
ROWS=N
3) drop the dev schema
4) import the prod schema (to dev)
5) import the dev-schema (to dev)
IGNORE=Y
CONSTRAINTS=N*
GRANTS=N*
INDEXES=N
ROWS=N*

the second import schould replace the prod objects (procedures, views) by the dev objects but leave the tables and table related objects. Parameters with * are alredady set during the export. Maybe one must not set them during import. If you set IGNORE=N then you will get a lot of error messages but maybe there are some other advantages. It will happen hat you import tables from dev that are not in prod. Changes to the table structure must be handled mynually.

I would prefere a method that extracts the procedures from the dev schema before it is replaced by the prod schema. Tools like Toad from Quest (as mentioned by @r.m in a comment) that can compare schemas an create scripts to implement the differences may help.

1) UserMode-Export from prod
2) Extract the procedures from dev into sql-scripts
3) drop dev schema
4) UserMode-Import to dev
5) drop procedures in dev
6) run the scripts created by the extraction process
7) compile all procedures

The following method leaves the dev structures and only imports the data from production. You will get errors during import of data if the column of a table has been renamed or removed in dev. Adding columns with constraints in dev tables or altering contraints in dev may also make the import of data fail for this tables. If some tables aren`t imported because of this errors dependent tables that were not changed in dev can also have problems when the data is imported. So a lot of complicated problems can arise. The basic idea is:

*) remove the data from the dev tables
*) disable contraints, triggers and indexes on the dev tables
*) import the data only from the prod tables to the dev tables
*) enable the constraints, triggers and indexes on the dev tables
*) import the data to the new dev tables and enable their constaints, triggers and indexes

1)user mode export the dev schema without triggers
ROWS=NO
TRIGGERS=NO
FILE=dev1.dmp
2) user mode export the dev schema again
ROWS=NO
TRIGGERS=NO
FILE=dev1.dmp
3) table mode export of dev tables not found on prod
TABLES=list_of_tables_in_right_order
TRIGGERS=Y
CONSTRAINTS=Y
INDEXES=YES
GRANTS=Y
ROWS=Y
STATISTICS=NO
dev3.dmp
4) drop the schema on dev
5) create the empty schema on dev again
6) import the empty dev schema without triggers, constraints and indexes
ROWS=NO
CONSTRAINTS=N
INDEXES=NO
FILE=dev1.dmp
7) import the indexes in a script
INDEXES=YES
ÌNDEXFILE=index.sql
FILE=dev1.dmp
8) user mode export the prod schema
CONSTRAINTS=NO
GRANTS=NO
TRIGGERS=NO INDEXES=NO FILE=prod1.dmp 9) table import the prod table data
TABLES=*
IGNORE=Y
CONSTRAINTS=N
INDEXES=N
GRANTS=N
FILE=prod1.dmp
10) create the indexes using index.sql with sqlplus
11) import table constraints and triggers
TABLES=*
CONSTRAINT=Y
TRIGGERS=Y
ROWS=N
FILE=dev2.dmp
12) import the dev tables you need
TABLES=list_of_tables_in_right_order
IGNORE=Y
CONSTRAINT=Y
GRANTS=Y
INDEXES=Y