Comparing SQL Server Schemas

If you're using Entity Framework code-first without database migrations, you'll be only too aware of the pain of updating the live database schema whenever you make changes to the model.

Assuming you're using a DropCreateDatabaseIfModelChanges initializer on your debug build, your development database will be automatically rebuilt whenever the model changes, but (assuming you don't want to lose data), any changes will need to be manually made on the live server before deployment.

The following scripts are what I use to identify the differences between development and live schemas.

1. Create a linked server for Live on your development machine

EXEC sp_addlinkedserver 'myLiveServer', N'SQL Server', 'www.myserver.com'
GO

EXEC sp_addlinkedsrvlogin
 @rmtsrvname = 'myLiveServer',
 @useself = 'FALSE',
 @locallogin = null,
 @rmtuser = 'sa', 
 @rmtpassword = 'myPassword'
GO

2. Compare the two schemas with reference to INFORMATION_SCHEMA

SELECT
 Local.TABLE_NAME,
 Local.COLUMN_NAME,
 Local.DATA_TYPE,
 Local.CHARACTER_MAXIMUM_LENGTH,
 Local.IS_NULLABLE,
 Remote.TABLE_NAME [Remote Table],
 Remote.COLUMN_NAME [Remote Column]
FROM myDatabase.INFORMATION_SCHEMA.COLUMNS [Local]
 FULL OUTER JOIN myLiveServer.myDatabase.INFORMATION_SCHEMA.COLUMNS [Remote]
  ON Remote.TABLE_NAME = Local.TABLE_NAME
  AND Remote.COLUMN_NAME = Local.COLUMN_NAME
  AND Remote.DATA_TYPE = Local.DATA_TYPE
  AND (
   Remote.CHARACTER_MAXIMUM_LENGTH = Local.CHARACTER_MAXIMUM_LENGTH
   OR (
    Remote.CHARACTER_MAXIMUM_LENGTH IS NULL
    AND Local.CHARACTER_MAXIMUM_LENGTH IS NULL
   )
  )
  AND Remote.IS_NULLABLE = Local.IS_NULLABLE
WHERE
 Local.TABLE_NAME IS NULL
 OR Local.COLUMN_NAME IS NULL
 OR Remote.TABLE_NAME IS NULL
 OR Remote.COLUMN_NAME IS NULL
ORDER BY
 Remote.TABLE_NAME,
 Remote.COLUMN_NAME,
 Local.TABLE_NAME,
 Local.COLUMN_NAME

3. Make the manual modifications

Running script (2) will return a set of modifications to be made on the live server.

Items that appear with 'Remote Table' and 'Remote Column' as NULL need to be added to the live server, according to the details given in the other columns.  Items that have entries for 'Remote Table' and 'Remote Column' but NULLs elsewhere need to be removed from the live server.

So, in the example shown below, a new column HomeAddress of type nvarchar(max) needs to be added to the Students table on the live server.  And Telephone2 needs to be removed from the Students table.

4. Update the hash

Script (2) can be run as many times as needed.  When it returns no rows, you know that the schemas on both development and live servers are the same.  In order to let Entity Framework work with the new version of the schema, you'll also need to update the hash manually - copying the hash that was generated automatically on the development server.

UPDATE myLiveServer.myDatabase.dbo.EdmMetadata
SET ModelHash = (SELECT ModelHash FROM myDatabase.dbo.EdmMetadata)

5. Copy over the code

Now you can deploy the new version of the code onto the live server, safe in the knowledge that the required schema is ready and waiting.