多个相同结构的表的字段的修改、添加

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

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

转载于:https://www.cnblogs.com/AlexLeeLi/p/7249937.html