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

翻譯

搜尋

複製