Unanswered: Export the database schema / structure to a file server
I would really appreciate if any of you can share your experience on the following:
Database-1: Oracle 8i/9i and
Database-2: SQL Server v65/7/2000
I am looking for a solution to extract all the database schema/structure to a pre-defined location onto a file server. I wanted to configure this thru CRON job (for unix) and Microsoft Task Scheduler (for Windows). Also this solution should work for all databases (must work with Oracle, SQL Server). The exported file should not be just a dump file (.dmp), assuming only oracle can read .dmp file.
What are the possible ways to accomplish this.
I am sure, several tools would be available to accomplish this (like Embarcadero Change Manager), but solution may not be cost effective. Can we write any program to accomplish the same?
I would really appreciate your inputs.
For what it's worth ... The times I have done this type of thing, it's been
for a "realitly small" number of tables ...
.. In Oracle, run SQLPlus and write the rows to a spooled text file that is TAB delimited (or comma delimited) ... Spool a file, issue select, close spool
... Repeat this for as many tables as needed ... This tab delimited file can be brought into SQLServer from DTS
set linesize 120
set header off
set pagesize 0
set feedback off
set trimspool on
... SQLServer - Create a DTS job that will write a tab or comma delimited text file for the tables needed ...
select username||chr(9)||sid||chr(9)||serial#||chr(9)||pr ogram from v$session;