批量给数据库表加字段
declare @tablename varchar(50) ,@sql varchar(1000) ,@dbname varchar(50) set @dbname=DB_NAME() declare addcolumn_Cusor cursor for select name from sys.tables open addcolumn_Cusor fetch next from addcolumn_Cusor into @tablename while(@@fetch_status=0) begin set @sql='IF NOT EXISTS (SELECT 1 FROM '+@dbname+'.dbo.SYSCOLUMNS WHERE ID=OBJECT_ID('''+@dbname+'.dbo.'+@tablename+''') AND NAME=''CreateDate'')' set @sql += 'alter table '+@tablename+' add CreateDate datetime default(getdate())' set @sql+='IF NOT EXISTS (SELECT 1 FROM '+@dbname+'.dbo.SYSCOLUMNS WHERE ID=OBJECT_ID('''+@dbname+'.dbo.'+@tablename+''') AND NAME=''Creator'')' set @sql += 'alter table '+@tablename+' add Creator nvarchar(12)' set @sql+='IF NOT EXISTS (SELECT 1 FROM '+@dbname+'.dbo.SYSCOLUMNS WHERE ID=OBJECT_ID('''+@dbname+'.dbo.'+@tablename+''') AND NAME=''UpdateDate'')' set @sql += 'alter table '+@tablename+' add UpdateDate datetime default(getdate())' set @sql+='IF NOT EXISTS (SELECT 1 FROM '+@dbname+'.dbo.SYSCOLUMNS WHERE ID=OBJECT_ID('''+@dbname+'.dbo.'+@tablename+''') AND NAME=''Updator'')' set @sql += 'alter table '+@tablename+' add Updator nvarchar(12)' exec(@sql) fetch next from addcolumn_Cusor into @tablename end close addcolumn_Cusor deallocate addcolumn_Cusor
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步