MSSQL遍历数据库根据列值查询数据
--受理编号
declare @slbh varchar(100),@searchColumn varchar(100)
--设置被查询列值
set @slbh='201703160009'
--设置搜索列名
set @searchColumn='SLBH'
declare @tableName varchar(50)
declare @sql nvarchar(max),@count int
set @sql=N''
set NOCOUNT ON
--优先输出表，再到视图
declare tableCursor cursor for select name from sysobjects where xtype='U' --OR xtype='V'
order by xtype asc,name asc
open tableCursor
Fetch next from tableCursor into @tablename
while @@FETCH_STATUS=0
begin
if exists(select name from sys.syscolumns where id=OBJECT_ID(@tableName) and name=@searchColumn)
begin
set @sql='select @count=count(*) from '+@tableName+' where '+@searchColumn+'='''+@slbh+''''
exec sp_executesql @sql,N'@count int output',@count output
if @count>0
begin
select '表名:'+@tableName
set @sql='select * from '+@tableName+' where '+@searchColumn+'='''+@slbh+''''
exec (@sql)
end
end
Fetch next from tableCursor into @tablename
end
close tableCursor
deallocate tableCursor
set NOCOUNT OFF
