Technical Article

Generate add / alter script for missing / different type columns

,

This query generate add/alter script for missing or different columns. Execute on source database and copy resultset. Execute generated script on any database (may be all customer databases) Missing columns will be created and different types will change as source schema.

Select 
'if not exists(select * from dbo.sysobjects so left outer join dbo.syscolumns sc on (so.id=sc.id)
  where (so.id = object_id(N''[dbo].['+t.name+']'')) and (OBJECTPROPERTY(so.id, N''IsUserTable'')=1) and (sc.Name=N'''+c.name+''') )
    ALTER TABLE '+t.name+' ADD [' + c.name + '] ' + ty.name + (CASE WHEN ty.name like '%char%' THEN '('+(CASE WHEN c.max_length<0 THEN 'MAX' ELSE CAST(c.max_length/(CASE WHEN ty.system_type_id in (231,239) THEN 2 ELSE 1 END) as nvarchar) END)+')' ELSE '' END) + (CASE WHEN c.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END)+'
GO

if exists(select * from dbo.sysobjects so left outer join dbo.syscolumns sc on (so.id=sc.id)
  where (so.id = object_id(N''[dbo].['+t.name+']'')) and (OBJECTPROPERTY(so.id, N''IsUserTable'')=1) and (sc.Name=N'''+c.name+''') and ((sc.xusertype<>'''+CAST(ty.user_type_id as varchar)+''') or (sc.length<>'''+CAST(c.max_length as varchar)+''')))
    ALTER TABLE '+t.name+' ALTER COLUMN [' + c.name + '] ' + ty.name + (CASE WHEN ty.name like '%char%' THEN '('+(CASE WHEN c.max_length<0 THEN 'MAX' ELSE CAST(c.max_length/(CASE WHEN ty.system_type_id in (231,239) THEN 2 ELSE 1 END) as nvarchar) END)+')' ELSE '' END) + (CASE WHEN c.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END)+'
GO

'
from sys.tables t
LEFT JOIN sys.all_columns c on t.object_id=c.object_id
LEFT JOIN sys.types ty on ty.user_type_id = c.user_type_id
Order by t.name, c.column_id

Rate

4 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (4)

You rated this post out of 5. Change rating