问题描述
有没有人有在整个数据库中搜索给定值的好方法?我有一个我正在寻找的特定字符串，它在 TableA 中，它也是其他一些表 TableB 的 FK，但我不知道那是哪个表/列.
Does anyone have a good method for searching an entire database for a given value?I have a specific string I'm looking for, it's in TableA, and it's also a FK to some other table, TableB, except I don't know which table/column that is.
假设有数以百万计的表格，而我不想查看所有表格，并且可能必须在几种不同的情况下执行此操作，那么最好的方法是什么?
Assuming there's a jillion tables and I don't want to look through them all, and maybe will have to do this in several different cases, what would be the best way?
由于我不想要 Code-SQL 桥，所以我唯一的全 SQL 想法是:
Since I didn't want a Code-SQL bridge, my only all-SQL idea was:
select tablename and column_name from INFORMATION_SCHEMA.COLUMNS
...然后使用游标浏览所有列，对于 nvarchar 的所有数据类型，我将执行动态 SQL，例如:
...then use a cursor to flip through all the columns, and for all the datatypes of nvarchar I would execute dynamic SQL like:
SELECT * from @table where @column = @myvalue
不用说，这很慢，而且很占用内存.
Needless to say, this is slow AND a memory hog.
有人有什么想法吗?
推荐答案
这里有几个链接讨论如何做到这一点:
Here are a couple of links talking about how to do this:
- http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2
- http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
他们都使用了您希望避免的方法.优化它们，以便它们只搜索作为外键的列，从而通过消除不必要的表的搜索来提高它们的性能.
Both of them use the approach you were hoping to avoid. Refine them so that they only searched columns that were foreign keys should improve their performance by eliminating the searching of unnecessary tables.
这篇关于SQL超级搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！