Comparing Production and Development Schemas

A common task I that I perform is updating a staging or production SQL server with the changes I've made to my local development database. The way I do it is fairly basic but it works quite well in practice.

I know this works with SQL Server 2005, but I think it should also work with SQL Server 2000. The idea should work on almost any database system that allows you to query for table and column metadata.

Here's the query I run on the databases:

SELECT table_name, column_name

FROM information_schema.columns

WHERE table_name IN

(

    SELECT table_name

    FROM information_schema.tables

    WHERE table_type = 'BASE TABLE'

)

ORDER BY table_name, column_name

Then, I just take both outputs of this query and stick them into WinMerge to get a schema comparison. I then proceed to update the staging or production database by hand, as is often necessary.

Take note that this query will only return columns that are in base tables, as you probably noticed by taking a look at the where clause. If you want to include the columns of views, just take out the where clause like so:

SELECT table_name, column_name

FROM information_schema.columns

ORDER BY table_name, column_name