SQL Server在所有表中查詢（在整個庫搜尋內容）

张载zz發表於2024-04-07
SQLServer

SQL Server在所有表中查詢（在整個庫搜尋內容）

declare @Str nvarchar(max), @tableName varchar(50), @colName varchar(50), @rowCount int

select a.name tableName, b.name Colname, 0 as IsFound into #t1
from sysobjects a join syscolumns b on a.id=b.id join systypes c on b.xtype=c.xtype
where a.[type]='U' and c.name in ('varchar', 'nvarchar', 'char', 'nchar') --這裡是設定欄位的型別，以縮小範圍

declare _c1 cursor for select Colname, tableName from #t1
open _c1
fetch next from _c1 into @colName, @tableName
while @@FETCH_STATUS=0 begin
--print @Str
    select @Str='select @rowCount=count(1) from ['+@tableName+'] where ['+@colName+'] like ''%崔健%''' --這裡是要查詢的內容
    exec sp_executesql @Str, N'@rowCount int output', @rowCount output
    if @rowCount>0 update #t1 set IsFound=1 where ColName=@colName and tableName=@tableName
fetch next from _c1 into @colName, @tableName
end
close _c1
deallocate _c1
select * from #t1 where IsFound=1
drop table #t1

轉載位置：https://blog.csdn.net/jeesr/article/details/91367909

相關文章