批量给数据库表加字段

浏览数：16 / 时间：2015年06月12日

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