Solved

SQL query that returns records with 'mytext' found in any column?

Posted on 2009-05-06
6
206 Views
Last Modified: 2012-05-06
Is there a way to build a query that will return all records with a specified text found in any of the  columns?

I'm trying to implement a global seach on our database for when a keyword is known but the correct column is not known.

If not . . . . how would I go about building a stored procedure that does this?

Thanks!
0
Comment
Question by:lthames
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24319893
That's been done and here's a link to one version of it.

I googled "sql search all tables" and that's the first hit.  Run the same search and you'll get more.

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
0
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24323891
hey

please check modified query in the above link posted by brandon...

DECLARE @SearchStr nvarchar(100)   
   SET @SearchStr = 'television'  
     
   CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))  
  
   SET NOCOUNT ON  
   DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)  
  
   SET  @TableName = ''  
   SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')  
  
   WHILE @TableName IS NOT NULL  
   BEGIN  
         SET @ColumnName = ''  
  
         SET @TableName =   
         (  
               SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))  
               FROM INFORMATION_SCHEMA.TABLES  
               WHERE       TABLE_TYPE = 'BASE TABLE'  
                     AND   QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName  
                     AND   OBJECTPROPERTY(  
                                 OBJECT_ID(  
                                       QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)  
                                        ), 'IsMSShipped'  
                                        ) = 0  
         )  
  
         WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)  
         BEGIN  
               SET @ColumnName =  
               (  
                     SELECT MIN(QUOTENAME(COLUMN_NAME))  
                     FROM INFORMATION_SCHEMA.COLUMNS  
                     WHERE       TABLE_SCHEMA      = PARSENAME(@TableName, 2)  
                           AND   TABLE_NAME  = PARSENAME(@TableName, 1)  
                           AND   DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar',  'numeric','decimal', 'double', 'money')  
                           AND   QUOTENAME(COLUMN_NAME) > @ColumnName  
               )  
  
               IF @ColumnName IS NOT NULL  
               BEGIN  
                     INSERT INTO #Results  
                     EXEC  
                     (  
                           'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)   
                           FROM ' + @TableName + ' (NOLOCK) ' +  
                           ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2  
                     )  
               END  
         END     
   END  
  
   SELECT distinct ColumnName, ColumnValue FROM #Results  
  
DROP TABLE #Results

Open in new window

0
 

Author Comment

by:lthames
ID: 24325280
This is a good start . . .but I only want to search all columns of ONE table (basically a support issue table and I don't know if the keyword will be in the subject, body, module name, contract number, etc.)

I think I can take the above procedure and modify it to work with just one table, but I would like to make sure there isn't a better way to do it if I only need results from one table.

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24328357
Check the below code which might meet your requirement...
please let me know if there is any issue as I have not executed it...

DECLARE @SearchStr nvarchar(100), @TableName nvarchar(256)   
   SET @SearchStr = 'television'
   SET @TableName = 'Emp'
     
   CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))  
  
   SET NOCOUNT ON  
   DECLARE @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)  
  
   SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')  
  
  
     WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)  
         BEGIN  
               SET @ColumnName =  
               (  
                     SELECT MIN(QUOTENAME(COLUMN_NAME))  
                     FROM INFORMATION_SCHEMA.COLUMNS  
                     WHERE       TABLE_SCHEMA      = PARSENAME(@TableName, 2)  
                           AND   TABLE_NAME  = PARSENAME(@TableName, 1)  
                           AND   DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar',  'numeric','decimal', 'double', 'money')  
                           AND   QUOTENAME(COLUMN_NAME) > @ColumnName  
               )  
  
               IF @ColumnName IS NOT NULL  
               BEGIN  
                     INSERT INTO #Results  
                     EXEC  
                     (  
                           'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)   
                           FROM ' + @TableName + ' (NOLOCK) ' +  
                           ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2  
                     )  
               END  
         END     
   
 SELECT distinct ColumnName, ColumnValue FROM #Results  
  
DROP TABLE #Results

Open in new window

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24328943
If it's just one table, and you only have 4 columns, then just write a simple query.



select * from SomeTable
where subject like '%SomeText%'
or body like '%SomeText%'
or modulename like '%SomeText%'
or contractnumber like '%SomeText%'

Open in new window

0
 

Author Closing Comment

by:lthames
ID: 31578748
This is exactly what I needed.  I had way more than 4 columns . . but I just added all of the columns and built the query in my program.

Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question