alertTable

declare @tableName varchar(500),@columns varchar(500)
set @tableName='TestBatch'
set @columns= '2'
set nocount on
declare @cols table (id int)
while(CHARINDEX(',',@columns)>0)
begin
insert into @cols
select CAST(SUBSTRING(@columns,1,CHARINDEX(',',@columns)-1) AS int)
set @columns=SUBSTRING(@columns,CHARINDEX(',',@columns)+1,LEN(@columns))
end
insert into @cols
select CAST(@columns AS int)

SELECT d.name 表名,
a.colorder 字段序号,
a.name 字段名,
(CASE WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN '√' ELSE '' END) 标识,
(CASE WHEN (
SELECT COUNT(*)
FROM sysobjects
WHERE (name IN
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid IN
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid IN
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 THEN '√' ELSE '' END) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 长度,
ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS 小数位数,
(CASE WHEN a.isnullable=1 THEN '√' ELSE '' END) 允许空,
ISNULL(e.text,'') 默认值,
ISNULL(g.[value],'') AS 字段说明
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype=b.xusertype
INNER JOIN sysobjects d ON a.id=d.id AND d.xtype='U' AND d.name <>'dtproperties'
LEFT JOIN syscomments e ON a.cdefault=e.id
LEFT JOIN sys.extended_properties g ON a.id=g.major_id AND a.colid = g.minor_id
WHERE d.name=@tableName
ORDER BY a.id,a.colorder

declare @CreateTableSQL nvarchar(max)
set @CreateTableSQL=''

print 'USE ['+db_name()+']'
print CHAR(13)+CHAR(10)+'Go'+char(13)+CHAR(10)+char(13)+CHAR(10)
print '/****** Object: Table [dbo].['+@tableName+'] Script Date: '+convert(varchar(50),getdate(),120)+ '******/'
print 'SET ANSI_NULLS ON'
print CHAR(13)+CHAR(10)+'GO'+char(13)+CHAR(10)+char(13)+CHAR(10)

 

SELECT @CreateTableSQL=@CreateTableSQL+CHAR(13)
+ 'BEGIN TRY' + CHAR(13)
+ ' BEGIN TRANSACTION;' + CHAR(13)
+
'ALTER TABLE dbo.'+@tableName+CHAR(13)+'ADD '+a.name+' '+b.name+
(CASE b.name WHEN 'decimal' THEN +'('+cast(COLUMNPROPERTY(a.id,a.name,'PRECISION') AS varchar(20))+','+cast(ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as varchar(20)) +') '
when 'nvarchar' then '('+(case cast(COLUMNPROPERTY(a.id,a.name,'PRECISION') AS varchar(20))
when '-1' then 'max'
else cast(COLUMNPROPERTY(a.id,a.name,'PRECISION') AS varchar(20))
end
)+') '
when 'nchar' then '('+cast(COLUMNPROPERTY(a.id,a.name,'PRECISION') AS varchar(20))+') '
when 'varchar' then '('+(case cast(COLUMNPROPERTY(a.id,a.name,'PRECISION') AS varchar(20))
when '-1' then 'max'
else cast(COLUMNPROPERTY(a.id,a.name,'PRECISION') AS varchar(20))
end
)+') '
when 'char' then '('+cast(COLUMNPROPERTY(a.id,a.name,'PRECISION') AS varchar(20))+') '
else '' END)+(CASE WHEN a.isnullable=1 THEN ' Null ' ELSE ' NOT NULL ' END)
+(case when (e.text is not null) then 'CONSTRAINT DF_'+@tableName+'_'+a.name+' DEFAULT '+cast(e.text as nvarchar(2000)) else '' end)
+ CHAR(13) + ' COMMIT TRANSACTION;' + CHAR(13)
+ 'END TRY' + CHAR(13)
+ 'BEGIN CATCH' + CHAR(13)
+ ' SELECT ERROR_NUMBER() as ErrorNumber, ERROR_MESSAGE() as ErrorMessage;' + CHAR(13)
+ ' ROLLBACK TRANSACTION;' + CHAR(13)
+ 'END CATCH;' + CHAR(13)
+CHAR(13)+CHAR(10)+'GO'+char(13)+CHAR(10)+char(13)+CHAR(10)
+ 'BEGIN TRY' + CHAR(13)
+ ' BEGIN TRANSACTION;' + CHAR(13)
+(case when (g.[value] is not null) then 'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''+cast(g.[value] as nvarchar(2000))+''' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N'''+@tableName+''', @level2type=N''COLUMN'',@level2name=N'''+a.name+'''' else '' end)
+ CHAR(13) + ' COMMIT TRANSACTION;' + CHAR(13)
+ 'END TRY' + CHAR(13)
+ 'BEGIN CATCH' + CHAR(13)
+ ' SELECT ERROR_NUMBER() as ErrorNumber, ERROR_MESSAGE() as ErrorMessage;' + CHAR(13)
+ ' ROLLBACK TRANSACTION;' + CHAR(13)
+ 'END CATCH;' + CHAR(13)
+CHAR(13)+CHAR(10)+'GO'+char(13)+CHAR(10)+char(13)+CHAR(10)
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype=b.xusertype
INNER JOIN sysobjects d ON a.id=d.id AND d.xtype='U' AND d.name <>'dtproperties'
LEFT JOIN syscomments e ON a.cdefault=e.id
LEFT JOIN sys.extended_properties g ON a.id=g.major_id AND a.colid = g.minor_id
WHERE d.name=@tableName and a.colid in (select id from @cols)

while (len(@CreateTableSQL)>=4000)
begin
print SUBSTRING(@CreateTableSQL,0,3000)
set @CreateTableSQL=SUBSTRING(@CreateTableSQL,3001,3000)
end

print @CreateTableSQL