/****** Object:  Table [pa_attachment_table]    Script Date: 11/15/2018 14:12:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON


GO
IF Not EXISTS (select * from syscolumns where id=object_id('_pa_template_attachmentTable') and name='xuhao')
Begin
/*创建字段*/
ALTER TABLE [_pa_template_attachmentTable] ADD [xuhao] int default 0  NULL
END

GO
IF Not EXISTS (select * from syscolumns where id=object_id('_pa_template_attachmentTable') and name='size')
Begin
/*创建字段*/
ALTER TABLE [_pa_template_attachmentTable] ADD [size] decimal(12, 2) Default 0 NULL
END



GO
--附件表添加xuhao和size字段
Declare @createTable varchar(500), @createSql varchar(5000),@tableName varchar(500),@fieldName varchar(100)

DECLARE listData CURSOR FOR  SELECT [tableName],[name] FROM [pa_field] where fieldType in('image','images','editor','file','files','textarea')
OPEN listData
fetch next from listData INTO @tableName,@fieldName
WHILE @@FETCH_STATUS=0
Begin
set @createTable=@tableName+'_'+@fieldName
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'['+@createTable+']') AND type in (N'U'))
BEGIN
set @createSql='ALTER TABLE ['+@createTable+'] ADD [xuhao] int default 0  NULL'
EXECUTE(@createSql)
set @createSql='ALTER TABLE ['+@createTable+'] ADD [size] decimal(12, 2) Default 0 NULL'
EXECUTE(@createSql)
END

fetch next from listData INTO @tableName,@fieldName
END
CLOSE listData 
DEALLOCATE listData

