SQL Server – How to Export DB Schema as ALTER Statements

migrationschemasql server

I have a 2 months old DB & I want to fetch new Schema for that DB without deleting the data in it using ALTER statements. Is it possible?

Currently I am taking a backup of table data of Old DB in some temporary DB & then replacing the Old DB with the new one & Inserting the backup data from the temporary DB. What could be the best solution to migrate the Schema?

Best Answer

I would write something like this, and first run in on the new database - to get the migration code, and then running resulting migration code on the old database - to alter the schema.

It's just a part to add missing columns and not complete. You should also check for is_identity, constraints, other data types and etc.

Maybe there is a complete code in the internet...

SELECT 'IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('''+t.name+''') AND name='''+c.name+''') 
ALTER TABLE ' + QUOTENAME(t.name) + '
ADD ' + QUOTENAME(c.name) + ' ' + tt.name
       + CASE WHEN tt.name IN ('varchar','nvarchar') THEN '(' + CAST(c.max_length as varchar(10)) + ')' 
              ELSE '' END
       + ' '
       + CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + char(13)+char(10)+char(13)+char(10)
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types tt ON tt.user_type_id = c.user_type_id

Result - migration code:

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='id') 
ALTER TABLE [users]
ADD [id] int NOT NULL IDENTITY

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='login') 
ALTER TABLE [users]
ADD [login] varchar(100) NOT NULL 

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='email') 
ALTER TABLE [users]
ADD [email] varchar(100) NULL
Related Question