Script to reverse engineering schema

[Thyagu]

Hi,

I'd like to reverse engineer the schema definitions in the SQL Server
on a daily basis and store them in a version control system.

Could anyone please let know if there are any tools to reverse engineer
the entire schema definitions in the SQL server. I'd like something
like the perl script 'dbschema.pl' for the Sybase ASE.

Regards,
Dellit.

[Erland Sommarskog]

Thyagu (td****@gmail.com) writes:

I'd like to reverse engineer the schema definitions in the SQL Server
on a daily basis and store them in a version control system.
Sounds like the wrong way to do. Would you version-control your C++
code by disassembling every day?
Could anyone please let know if there are any tools to reverse engineer
the entire schema definitions in the SQL server. I'd like something
like the perl script 'dbschema.pl' for the Sybase ASE.

Look at SQL Compare from Red Gate and see if it could work for you.

You can always do it from Enterprise Manager, but that's tedious to do
on a daily basis. But you could investigate to see how much work it would
take to do this in DMO. Or SMO if you are on SQL 2005. (I have not used
any of them, so I cannot assist with the details.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[sualeh.fatehi]

Dellit,

If you objective is to keep track of changes over time, and use a
version control system to keep track of changes over time, you can use
SchemaCrawler, a free open-source tool that can compare schemas as well
as data.

SchemaCrawler is a command-line tool to output your database schema and
data in a readable form. The output is designed to be diff-ed with
previous versions of your database schema.

http://schemacrawler.sourceforge.net/

All you will need is a JDBC driver, and any diff tool such as WinMerge.

Sualeh Fatehi.

[Stu]

I have a script that I use SCPTXFR in to check our database builds into
Visual Source Safe on a daily basis; it's actually very handy in a
development environment where you have multiple SQL developers poking
into each other's code.

DMO is nice, but I haven't figured out how to handle depndancies yet;
without that knowledge, the build script is ordered by table name,
which is not particularly useful when you are trying to rebuild a
database.

Stu