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
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home