Solved

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

Posted on 2009-05-06
6
202 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
  • 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now