Searching for a value in all tables using SQL Server Management Studio

Feedback


Solution 1:

The speed of returning a substring search can vary depending on the amount of data you have.

If you have the ability to predefine the tables and desire a unified search that scans and retrieves matches from all tables, you will require a solution similar to the following.

SELECT 
    'Items' as table_name,
    item_id as record_id,
    ItemName AS found
FROM
    Items
WHERE
    ItemName LIKE '%cocacola%'
UNION
SELECT 
    'Categories' as table_name,
    CategoryID AS record_id,
    Name AS found
FROM
    Categories
WHERE
    Name LIKE '%cocacola%'
UNION
SELECT 
    'Brands' as table_name,
    BrandID AS record_id,
    Name AS found
FROM
    Brands
WHERE
    Name LIKE '%cocacola%'

The UNION operation will combine the results of one query with another.

If you possess a large amount of data, the processing speed will be reduced.


Solution 2:


Your solution is not wrong as you execute three queries, each targeting a separate table. Depending on your specific use case, this approach should be acceptable.

To search all tables with just one query, you have the option to join the tables. However, this approach may be slower compared to executing three separate queries, as the database needs to match the values together.

SELECT *
  FROM Items
  FULL OUTER JOIN Categories ON Categories.CategoryID = Items.CategoryID
  FULL OUTER JOIN Brands ON Brands.BrandID = Items.BrandID
WHERE Items.ItemName LIKE 'cocacola'
  AND Categories.Name LIKE 'cocacola'
  AND Brands.Name LIKE 'cocacola'

In the event that this query yields a match in the category name, the category will be displayed for each item that is linked to it.


Solution 3:


It seems like utilizing a union to merge the outcomes of the three queries could be worth considering.

For example:

SELECT ItemID, ItemName
  FROM Items
 WHERE ItemName = 'cocacola'
 UNION
SELECT CategoryID, Name
  FROM Categories
 WHERE Name =  'cocacola'
 UNION
SELECT BrandID, Name
  FROM Brands
 WHERE Name = 'cocacola'

It is important to ensure that each part of the query in a union returns columns with the same number, datatype, and order.

Frequently Asked Questions

Posted in Sql