--修改多个相同结构的表的字段

declare @TableName varchar(50);

declare cur_tableNames cursor for select name from sysobjects where type = 'U' and Name like 'box_mac_%' order by name ;

open cur_tableNames

fetch next from cur_tableNames into @TableName

while @@FETCH_STATUS=0

begin

print 'exec sp_rename ''['+@TableName+'].[days5d_quiet_start]'', ''days5_quiet_start'', ''COLUMN'''

EXEC ( 'exec sp_rename ''['+@TableName+'].[days5d_quiet_start]'', ''days5_quiet_start'', ''COLUMN''')

fetch next from cur_tableNames into @TableName

end

close cur_tableNames

 

--存储过程 （多个相同结构的表的字段添加）

USE [tongji.yxyxh]

GO

/****** Object: StoredProcedure [dbo].[UpdateTable_box_mac] Script Date: 07/28/2017 13:59:26 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[UpdateTable_box_mac]

as

begin

declare @TableName varchar(50);

declare cur_tableNames cursor for select name from sysobjects where type = 'U' and Name like 'box_mac_%' order by name;

open cur_tableNames

fetch next from cur_tableNames into @TableName

while @@FETCH_STATUS=0

begin

exec( 'alter table '+@TableName +' add [days3_start] [bit] NULL')

exec( 'alter table '+@TableName +' add [days5_start] [bit] NULL')

exec( 'alter table '+@TableName +' add [days3_quiet_start] [bit] NULL')

exec( 'alter table '+@TableName +' add [days5_quiet_start] [bit] NULL')

fetch next from cur_tableNames into @TableName

end

close cur_tableNames

end