0

I have a SQL Server database that has around 30 tables. I need to be able to search for a keyword (could be string, number, float, date, time) throughout the database and list all the rows where that particular keyword occurs. If it so happens that the keyword is in multiple rows spread across multiple tables, they still need to be displayed. For example, rows containing the keyword from table1 and then some space and then the rows containing the keyword from table 2 and so on.

I have found solutions on this platform such as this: https://stackoverflow.com/a/27361062/13942396 and Narayana Vyas's stored procedure.

But the issue is it displays the table name and te column name and the keyword value. It doesn't display the values of the rest of the columns in that particular row that has the keyword.

Any help would be appreciated!

kushaalk
  • 13
  • 2
  • 1
    Add `*` to the dynamic SELECT query from the other answer? – David Browne - Microsoft Aug 27 '21 at 12:32
  • You could just alter it to not have the table look up. it might be some work on your end. Also, you could alter it to loop through the sys.tables.name list. Im pretty certain going through each column in each table is going to be the absolute worst in terms of performance. This is something I would think you wouldnt run in production during normal business hours, or at least exclude mission critical table. Or better yet take a back up and do this search locally. – Doug Coats Aug 27 '21 at 12:54
  • 1
    Logically you cannot accomplish what you imply. A query (or a table) has a fixed schema - a fixed number of columns with specific datatypes. That logically flows through to any graphical presentation. To show a "grid" of all rows from all tables that match, you need to force the output into that concept of a fixed set. Think about that and how you wish to display that information within your GUI first. And note that any "solution" using NOLOCK should be considered with caution. – SMor Aug 27 '21 at 12:57

0 Answers0