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
标签:colName,SQL,tableName,Server,rowCount,表中,c1,t1,select From: https://www.cnblogs.com/zhangzaizz/p/18119118