Database Schema

[jack]

Hi

In my project there's a frequent change in database schema of
production and testing due to the weekly production.

so this talks lot of effort to figure out which is the exact table
which we have made the change

so for the purpose i thought of making an small application which will
list me the tables or columns which are not in sync.


I tried some of the stuff but was not successful in it

i was able to get the database tables but executing the below query.

Select * from sysobjects
where xtype='u

But when i tried to get the column schema to compare it with the table
from another database table, i was unable to do so.

below is my unsuccessful query string which i executed in the
SQLCommand object


Select * from sys.all_columns where object_id= object_id('TableName')


Please help

 

[Nicholas Paldino [.NET/C# MVP]]

jack,

Not that I would disuade you from trying to do it yourself, but have you
looked at any tools to do this? There are some very good tools on the
market that can determine changes in schema, and it would probably save you
a lot of time coding this yourself.

I like the tools from Red Gate in particular (this is the link to SQL
Compare):

http://www.red-gate.com/products/SQL_Compare/index.htm?gclid=CMWs0PujxJECFSBMGgodv3FSDQ

Apex also makes a tool to do this:

http://www.apexsql.com/sql_tools_diff.asp

And another one I found on Google:

http://www.sqlaccessories.com/SQL_Examiner/?src=google1_1&gclid=CLOJsIukxJECFReQGgodZVagCA

 

[Sualeh Fatehi]

The free, open-source SchemaCrawler for SQL Server tool is desinged to
do just this. You can take human-readable snapshots of the schema and
data, for later comparison. Comparisons are done using a standard diff
tool such as WinMerge. SchemaCrawler outputs details of your schema
(tables, views, procedures, and more) in a diff-able plain-text format
(text, CSV, or XHTML). SchemaCrawler can also output data (including
CLOBs and BLOBs) in the same plain-text formats.

SchemaCrawler is available at SourceForge:
http://schemacrawler.sourceforge.net/

Sualeh Fatehi

 

[jack]

Thanks for replying me.


have surfed on net and found some of the interesting tools like
tablediff.exe which is the standard tool of sql and can be used with
the command prompt arguments

but still not very straight forward way of getting all the table
difference in one shot.