SQL 指定后缀表名执行批量操作

发布日期:2021-06-25

SQL 指定后缀表名执行批量操作



declare @maxCount int 
select  ROW_NUMBER() over(order by id) as rowId,s.id,s.name 
into #base
from sysobjects as s  
where s.name like 'Kao%' 
set @maxCount = (select  count(1) from #base)
declare @index int = 1
while(@index<=@maxCount)
begin
declare @tableName nvarchar(50)
select @tableName =name from #base where rowId = @index
declare @sql nvarchar(max)
set @sql=N'select top 2  '''' as  '+@tableName+',*from '+@tableName+' '
exec(@sql)
set @index = @index+1
end
drop table #base




检测库内表中没有某个字段时进行的批量操作
use Xinfo_Lg

declare @maxCount int 
--1.批量检测没有cls的内邮表
select  ROW_NUMBER() over(order by id) as rowId,s.id,s.name 
into #base
from sysobjects as s  
where s.name like 'FmMsg_%' and s.name not in (
select distinct s.name  from sysobjects as s
left join syscolumns c on c.id = s.id
left join systypes t on c.xtype=t.xtype
where s.name like  'FmMsg_%' and c.name ='cls'
)
--2.批量将检测到的没有cls的内邮用户表进行批量添加cls字段
set @maxCount = (select  count(1) from #base)
declare @index int = 1
while(@index<=@maxCount)
begin
declare @tableName nvarchar(50)
select @tableName =name from #base where rowId = @index
declare @sql nvarchar(max)
set @sql=N'alter table '+@tableName+' add cls nvarchar(50) null'
exec(@sql)
set @index = @index+1
end
drop table #base



凡是表名前缀或者后缀带***的执行批量操作