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

Remove All Tables and Constraints from a Database Using T-SQL

You've had this problem before: You don't want to drop a database completely, but you do want to drop all the tables. Try to drop your tables in the wrong order and you're slapped with an error regarding referential constraints. I've created this script to ease the burden. It first drops all the constraints, and then it drops all the tables. Let me know if this doesn't work on your SQL Server database. Here's a warning for those who didn't bother to read this paragraph:

WARNING: The following script will delete all the tables in your database.

On to the script:

DECLARE @TableName NVARCHAR(MAX)

DECLARE @ConstraintName NVARCHAR(MAX)

DECLARE Constraints CURSOR FOR

 SELECT TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

 

OPEN Constraints

FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName

 

WHILE @@FETCH_STATUS = 0

BEGIN

 EXEC('ALTER TABLE [' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + ']')

 FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName

END

 

CLOSE Constraints

DEALLOCATE Constraints

 

DECLARE Tables CURSOR FOR

 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

 

OPEN Tables

FETCH NEXT FROM Tables INTO @TableName

 

WHILE @@FETCH_STATUS = 0

BEGIN

 EXEC('DROP TABLE [' + @TableName + ']')

 FETCH NEXT FROM Tables INTO @TableName

END

 

CLOSE Tables

DEALLOCATE Tables

Enjoy. Let me know if this doesn't work for you.