Generate Alter Script for Adding Column in Existing table

Some times we are facing issues how to migrate the table(New version) column to old one table (Old version). for that we can  generate the alter script of table.

We have use other option as DB  compare etc.

if exists (

select * from tempdb.dbo.sysobjects o

where o.xtype in ('U')




and o.id = object_id(N'tempdb..#tempTable')

)

DROP TABLE #tempTable;







SELECT

'' + SC.Name + ' ' + ' ' + ST.NAME + ' ' +

CASE

WHEN (ST.Name='bigint') THEN ''

WHEN (ST.Name='binary') THEN ' (' + CONVERT(varchar(10), SC.Length) + ')'

WHEN (ST.Name='bit') THEN ''

WHEN (ST.Name='char') THEN ' (' + CONVERT(varchar(10), SC.Length) + ')'

WHEN (ST.Name='datetime') THEN ''

WHEN (ST.Name='decimal') THEN ' (' + CONVERT(varchar(10), SC.Prec) + ', ' + CONVERT(varchar(10), SC.Scale) + ')'

WHEN (ST.Name='float') THEN ''

WHEN (ST.Name='image') THEN ''

WHEN (ST.Name='int') THEN ''

WHEN (ST.Name='money') THEN ''

WHEN (ST.Name='nchar') THEN ' (' + CONVERT(varchar(10), SC.Length) + ')'

WHEN (ST.Name='ntext') THEN ''

WHEN (ST.Name='numeric') THEN '(' + CONVERT(varchar(10), SC.Prec) + ', ' + CONVERT(varchar(10), SC.Scale) + ')'

WHEN (ST.Name='nvarchar') THEN ''

WHEN (ST.Name='real') THEN ''

WHEN (ST.Name='smalldatetime') THEN ''

WHEN (ST.Name='smallint') THEN ''

WHEN (ST.Name='smallmoney') THEN ''

WHEN (ST.Name='sql_variant') THEN ''

WHEN (ST.Name='sysname') THEN ''

WHEN (ST.Name='text') THEN ''

WHEN (ST.Name='timestamp') THEN ''

WHEN (ST.Name='tinyint') THEN ''

WHEN (ST.Name='uniqueidentifier') THEN ''

WHEN (ST.Name='varbinary') THEN ' (' + CONVERT(varchar(10), SC.Length) + ')'

WHEN (ST.Name='varchar') THEN ' (' + CONVERT(varchar(10), SC.Length) + ')'




ELSE ' ' END + ' ' + CASE WHEN SC.isnullable = 0 THEN ' NOT NULL ' ELSE ' NULL ' END

+ ' ' + CASE WHEN SCmnts.text is not null THEN ' DEFAULT ' + SCmnts.text ELSE '' END AS ColumnScript,

SC.Name into #tempTable

FROM SYSCOLUMNS SC

INNER JOIN SYSTYPES ST ON SC.xusertype = ST.xusertype

LEFT JOIN SYSCOMMENTS SCmnts ON SC.cdefault = SCmnts.ID

WHERE SC.ID IN (SELECT ID FROM SysObjects WHERE [NAME] ='SETUP_USERROLEDETAILS')select *,

'IF NOT EXISTS(SELECT * FROM sys.columns WHERE name

='''+Name +'''and object_id=object_id (''dbo.SETUP_USERROLEDETAILS''))

begin

alter table dbo.SETUP_USERROLEDETAILS ADD '+

ColumnScript+'

END' from #tempTable