从该表的任何项目中搜索表 [英] search a table from any items of that table

问题描述

所有我需要帮助来编写一个SP，该SP可以从表的任何数据进行搜索，这意味着我有一个表，其中包含100个要基于这些列的任何字段进行搜索的列.我应该在where子句中指定wat ..

谢谢

Hi all I need a help to write a SP which searches from any data of the table means i have one table which contains 100 columns i want to search based on any field of these column.. wat should i specify in where clause..

Thanks

推荐答案

在寻找类似的解决方案时，我找到了此链接.
该功能可以搜索数据库中的任何表.您需要根据需要自定义广告.

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm [

While searching for similar solution i found this link.
The function can search any table in database.you need to customise it ad per your requirement.

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm[^]

Hope this helps.

没有聪明的方法可以做到这一点.您有100个colmuns，唯一的方法是动态构建sql查询并使用LIKE来搜索文本. (如果所有列都是文本?)

您需要达到以下目标:

There are no smart way of doing this. You have 100 colmuns and the only way is to build a sql-query dynamically and use a LIKE to search for text. (if all columns are text?)

You need to achive:

SELECT * FROM Table WHERE
         Column1 LIKE '%query%'
      OR Column2 LIKE '%query%'
      OR Column3 LIKE '%query%'
and so on

您可以查询一些系统表以列出所有列并对其进行遍历并生成所需的sql，但这太麻烦了.

要在sql中进行搜索，您需要为性能设置一些索引，在向大型数据库发出查询之前，有很多事情需要考虑.

我的提示是寻找某些第三方搜索引擎工具，如Lucene.net或类似工具.或手动编写查询.

第三种选择是生成包含2列(id和text)的第二个表，其中包含要搜索的表中所有列的值，然后在该列中进行搜索.
缺点是它将占用数据库中的一些空间，并且您无法为每一列分配权重.

You could query some system tables to list all columns and iterate through them and generate the sql you need, but that would be nasty.

To search in sql you need to set up some indexing for performance and there''s a lot of things to think about before firing a query to a large database.

My tips is to either look for some 3rd party search engine tools like Lucene.net or similar. Or do the job writing the query by hand.

A third option is to generate a second table with 2 columns (id and text) holding the values from all the columns in the table you want to search, and then search in that column.
A few downsides is that it will take up some space in your database and you can''t weight score for each column.

CREATE TABLE SearchTable(
  [Id] [int] NOT NULL,
  [Text] [varchar](max) NOT NULL -- you should index this column
) 

然后从第一个表中复制数据:

And then copy data from your first table:

INSERT INTO SearchTable 
SELECT Id, Column1 + ' ' + Column2 + ' ' + Column3 -- and so on
FROM OriginalTable

然后可以搜索SearchTable:

Then you can search SearchTable:

SELECT OriginalTable.* FROM OriginalTable
INNER JOIN SearchTable ON OriginalTable.Id = SearchTable.Id
WHERE SearchTable.Text LIKE '%query%'

可以使用临时表动态地动态"完成此操作，但是如果有大量数据，我会安排它在特定时间运行并进行索引.

This could be done dynamically "on the fly" using temp tables, but if theres a lot of data I would schedule this to run and do indexing at specific times.

wat应该我在where子句中指定.
列名称和相关搜索文本是什么！

wat should i specify in where clause..
Whatever is the column name and related search text!

WHERE @ColumnName = @ColumnSearchText

这篇关于从该表的任何项目中搜索表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！